Microsoft certified: Business Data analyst training
iMeta: 3-month training programme
Overview
This was a 3-month Microsoft certified training programme which covered the fundamentals of data analysis using Microsoft Excel, SQL server, python and Microsoft Power BI. The programme included class hours, self-study and assignments, adding up to nearly 200 hours of learning and practice. The modules in the course included:
- Data types and functions
- Models and exceptions
- Object-orientated programming (OOP)
- Data analytics and Big data
- Advanced data analysis and machine learning
Microsoft Excel
The Microsoft Excel module included the following topics:
- Basics: Navigation, formatting, conditional formatting and rules.
- Data cleaning: Formatting, data types, functions, sorting, filtering, removing duplicates and blanks, space trimming, un-stack data.
- Data import: CSV, web scraping.
- Complicated functions: SUMIF, AVERAGEIFS, COUNTIFS, IFS, IFERROR, XLOOKUP.
- Data analysis: Pivot tables, visualisations, dashboard design and slicers for specific audiences.
Key files:
SQL Server
The SQL server module included the following topics:
- Basics: Creation of databases, tables and data types
- Data cleaning: Checking imported tables, CONVERT, DATE
- Manipulating data: Using SELECT, WHERE, GROUP BY, SUM, DATENAME, DATEPART.
- Advanced queries: Using CASE, WHEN, THEN, ELSE
Key files:
Python
The python module included the following topics:
- Basics: Variables, print(), len(), looping, strings.
- In-built functions: Data types, input(), indexing, IF/ELSE/ELIF statements.
- Data structures: Lists, append(), insert(), arrays, tuples, dictionaries.
- Conditional Statements: IF/ELSE/ELIF statements, While.
- Functions: Creating new functions.
Key files:
Basics
.. code:: ipython3
#Position number 0 1 2 3 4
#Word h e l l o
a = 'Hello World'
print(a)
print(a[1]) #Selects the position 1 from the string, 'e'
Hello World
e
.. code:: ipython3
#Selects each character of the string with '\n' being a new line break
print('\n',a[0],'\n',a[1],'\n',a[2],'\n',a[3],'\n',a[4],'\n',a[5],'\n',a[6],'\n',a[7],'\n',a[8],'\n',a[9])
H
e
l
l
o
W
o
r
l
.. code:: ipython3
#Reads length of a string
len(a)
11
.. code:: ipython3
#Looping (for loop)
a = 'Hello World'
for i in a:
print(i)
H
e
l
l
o
W
o
r
l
d
.. code:: ipython3
#Looping (while loop)
a = 'Hello World'
i = 0
while i<len(a):
print(a[i])
i+=1
H
e
l
l
o
W
o
r
l
d
.. code:: ipython3
#Searching a string
x = 'The best things in life are free'
if 'free' in x:
print('Yes it is present')
else:
print('No it is absent')
Yes it is present
.. code:: ipython3
#Searching a string (NOT IN)
x = 'The best things in life are free'
if 'free' not in x:
print('Yes it is absent')
else:
print('No it is present')
No it is absent
.. code:: ipython3
y = '1234567' #Only works with strings
if '7' in y:
print('Yes')
else:
print('No')
Yes
.. code:: ipython3
#Selecting parts of a string continued
a = 'Hello World!'
print(a[2:8]) #Characters 2 to 8
print(a[2:]) #Characters 2 to the end
print(a[:8]) #Characters begining to 8
print(a[0:12:2]) #Characters 0 to 12 jumping by 2 characters each time
llo Wo
llo World!
Hello Wo
HloWrd
IF ELSE ELIF
.. code:: ipython3
#Number program - ver 1
num1 = 13
num2 = 42
print(num1 + num2)
55
.. code:: ipython3
#ver2
num1 = int(input('Enter the first number'))
num2 = int(input('Enter the second number'))
print()
print('The answer is', num1+num2)
Enter the first number 34
Enter the second number 22
The answer is 56
.. code:: ipython3
#ver3
num1 = int(input('Enter the first number'))
num2 = int(input('Enter the second number'))
operator = int(input('Add or subtract? 1 to add, 2 to subtract'))
if operator == 1:
answer = num1 + num2
print(answer)
else:
answer = num1 - num2
print(answer)
print()
print('The answer is', answer)
Enter the first number 33
Enter the second number 22
Add or subtract? 1 to add, 2 to subtract 2
11
The answer is 55
.. code:: ipython3
#ver4
num1 = int(input('Enter the first number'))
num2 = int(input('Enter the second number'))
operator = int(input('Operator? 1 to add, 2 to subtract, 3 to multiply, 4 to divide'))
if operator == 1:
answer = num1 + num2
elif operator == 2:
answer = num1 - num2
elif operator == 3:
answer = num1*num2
elif operator == 4:
answer = num1/num2
print()
print('The answer is', answer)
Enter the first number 34
Enter the second number 22
Operator? 1 to add, 2 to subtract, 3 to multiply, 4 to divide 4
The answer is 1.5454545454545454
.. code:: ipython3
#Reverse indexing (check powerpoint)
word = 'Cheeseman'
print(word[::-1])
ehC
Data structures
.. code:: ipython3
#Python data structures
mylist = ['Addition', 'subtraction', 'multiplication', 'division']
mylist[3]
mixed_list= ['banana', 45.67,21,[32,45,32], 'cheese']
mixed_list[3]
[32, 45, 32]
.. code:: ipython3
mixed_list.append('Apples') #Adds to the list
mixed_list.insert(3,'Chicken')#Inserts value at position indicated, 3 in this case
.. code:: ipython3
print(mixed_list)
['banana', 45.67, 21, [32, 45, 32], 'cheese', 'Apples']
.. code:: ipython3
#ver4
num1 = int(input('Enter the first number'))
num2 = int(input('Enter the second number'))
operator = int(input('Operator? 1 to add, 2 to subtract, 3 to multiply, 4 to divide'))
operator_list = ['+','-','*','/']
print('What is', num1, operator_list[operator-1], num2)
if operator-1 == 0:
answer = num1 + num2
elif operator-1 == 1:
answer = num1 - num2
elif operator-1 == 2:
answer = num1 * num2
elif operator-1 == 3:
answer = num1 / num2
test_answer = int(input('Enter your answer'))
if test_answer == answer:
print('Correct, the answer is ', answer)
else:
print('Incorrect, the correct answer is', answer)
Enter the first number 45
Enter the second number 21
Operator? 1 to add, 2 to subtract, 3 to multiply, 4 to divide 3
What is 45 * 21
Enter your answer 3454
Incorrect, the correct answer is 945
.. code:: ipython3
#Create Hotel reservation programme
hotel_res = {'John Simon':'Room 45', 'Sally Wish':'Room 7', 'Scott Trevor': 'Room 85'}
name = input('Enter your name: ')
if name in hotel_res:
print('Welcome', name, 'your room number is: ', hotel_res.get(name))
Enter your name: John Simon
Welcome John Simon your room number is: Room 45
Conditional Statements
.. code:: ipython3
#Conditional statements
a = 13
b = 28
if a > b:
print(a,'is greater than', b)
elif b > a:
print(b,'is greater than', a)
elif a==b:
print(a,'is equal to', b)
28 is greater than 13
.. code:: ipython3
#Password system (nested if statements)
admin_user = 'admin'
admin_password = 'password123'
username = input('Enter your username: ')
password = input('Enter your password: ')
if username == 'admin':
if password == 'password123':
print('login successful')
else:
print('invalid password')
else:
print('invalid username')
Enter your username: admin
Enter your password: password123
login successful
.. code:: ipython3
#While loops
i = 1
while i < 6:
print(i)
i = i+ 1
1
2
3
4
5
.. code:: ipython3
i = 1
while i < 6:
print(i)
i *= 3 # i times by 3
1
3
.. code:: ipython3
x = 80
while x > 6:
print(x)
x -= 10
80
70
60
50
40
30
20
10
.. code:: ipython3
#Looping through a list
fruits = ['berry', 'banana', 12, 'apples', 25.32]
for i in fruits:
print(i, end = ' ') #end adds something at the end of each item, so a space here
berry banana 12 apples 25.32
.. code:: ipython3
#Functions
def testing():
print('Hello, this is my first function')
testing()
Hello, this is my first function
Functions
.. code:: ipython3
#Function tasks
#Exercise 1
x=34
while x <= 54:
print(x)
x = x+1
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
.. code:: ipython3
#Function tasks
#Exercise 2
y=1
sum_list = []
while y <= 100:
sum_list.append(y)
y = y + 1
sum_of_list = sum(sum_list)
print(sum_of_list)
5050
.. code:: ipython3
#Function tasks
#Execise 3
x = 10
while x >= 1:
print(x)
x = x-1
10
9
8
7
6
5
4
3
2
1
.. code:: ipython3
#Function tasks
#Exercise 4
x=1
while x <= 50:
if x % 2 == 0:
print(x)
x = x+1
2
4
6
8
10
12
14
16
18
20
22
24
26
28
30
32
34
36
38
40
42
44
46
48
50
.. code:: ipython3
#Functions task
#Exercise 5
storage = []
x= -1
while x < 0:
x = int(input('Enter a number please: '))
storage.append(x)
sum_storage = sum(storage)
print(sum_storage)
Enter a number please: -4
Enter a number please: -3
Enter a number please: 5
-2
.. code:: ipython3
#University exercise
program = "Accounting"
def program_details():
no_gr8 = int(input("Enter how many subjects to have grade 8: "))
University = input("Enter University name: ")
study_years = int(input("Enter how many years of studies: "))
job_avail= input("Enter lots of jobs or very few position: ")
salary = int(input("Enter entry level salary: "))
print()
print(f"""To study {program} from {University} \nYou need {no_gr8} grade 8 in GCSE core subjects
\nThe programme take {study_years} years to compete\n
\nThe good thing however is that there are {job_avail}
\nStarting salary is {salary} """)
program_details()
.. code:: ipython3
#Break [1,2,3,4,5]
i = 1
while i < 6:
print(i, end = ' ')
if i == 3:
break
i += 1
1 2 3
.. code:: ipython3
i = 0
while i < 6:
i += 1 #same as i = i + 1
if i == 3:
continue
print(i, end = ' ') #3 is not in the final output as it was removed into a continue
1 2 4 5 6
.. code:: ipython3
#Numpy
import numpy as np
import pandas as pa
.. code:: ipython3
a = np.array([1,2,3])
print(a)
[1 2 3]
.. code:: ipython3
b = np.zeros(2)
print(b)
[0. 0.]
.. code:: ipython3
c = np.ones(2)
print(c)
[1. 1.]
.. code:: ipython3
d = np.empty(2)
print(d)
[1. 1.]
.. code:: ipython3
e = np.arange(4)
print(e)
[0 1 2 3]
.. code:: ipython3
#0-D array
g = np.array(54)
print(g)
54
.. code:: ipython3
#1-D array
h = np.array([1,2,3,4,5])
print(h)
[1 2 3 4 5]
.. code:: ipython3
#2-D array
i = np.array([[2,3,4,5],[8,9,10,12]])
print(i)
[[ 2 3 4 5]
[ 8 9 10 12]]
Microsoft Power BI
The Microsoft Power BI module included the following topics:
- Data import: CSV and Excel
- Data transformation: Adding/transforming columns, formatting data types, extracting/splitting/parsing data, power query data distribution and profile. (Power Query)
- Relationships: Related keys, types of relationships and connections. (Model View)
- Measures: Quick and created measures using DAX (Data view)
- Reports: Creating different visualisations using summary statistics and measures, formatting, report design, colour schemes, customisations, slicers and filter interactions. (Report view)
Key files: