Prepare - Clean, transform, and load data in Power BI

Question 1

What is shaping the data?

Answer 1

Shaping data is about cleaning, simplifying and organising data to meet your needs and tell your story

Question 2

What are the 7 steps in shaping data?

Answer 2

Step 1 - Identify column headers and names
Check to make sure column heads are correct
Remove undescriptive columns

Step 2 - Promote headers
Power Query assumes that all data belongs in rows and sometimes does not recognise rows as headers

Step 3 - Rename Columns
Check to make sure columns are named correctly
Adopt name conventions

Step 4 - Remove top rows
Remove rows to make sure table has no blanks

Step 5 - Remove columns
Remove unnecessary columns

Step 6 - Unpivot columns (many to 2)
Changes headers to rows

Step 7 - Pivot columns (2 to many)
Change rows to headers

Question 3

What is best practice in shaping data?

Answer 3

Best practice
Remove unnecessary columns as early as possible to retain the most important information (do this before data model)

Best steps are

• Removing Columns
• Unpivot
• Pivot

Question 4

What is simplifying the data structure?

Answer 4

When you import data from multiple sources into Power BI Desktop, the data retains its predefined table and column names. You might want to change some of these names so that they are in a consistent format, easier to work with, and more meaningful to a user.

Question 5

What are the 4 steps in simplifying the data structure?

Answer 5

Rename a query
It’s good practice to change uncommon or unhelpful query names to names that are more obvious or that the user is more familiar with.
Remove unnecessary suffix’s and prefix’s

Replace values
Some values are not very informative or misspelled

Replace null values
Often data sources may contain a null (nothing entered) instead of a zero value
This is very important because otherwise calculations will not work

Remove duplicates
Remove duplicates to keep unique values

Question 6

What is best practice for naming tables, columns, and values?

Answer 6

General
Use language and abbreviations commonly used within your organisation
Replace underscores with spaces
Give tables, columns, measures descriptive business terms
Be consistent with abbreviations and prefaces
Short abbreviations can cause confusion
Remove prefixes and suffixes

Values
Think about how values might appear on a report or visual
Values that are too long might be difficult to read on a visual
Values that are too short might be difficult to interpret
Try to avoid acronyms as much as possible

Question 7

Why evaluate column data types?

Answer 7

When you import a table from any data source, Power BI Desktop automatically starts scanning the first 1,000 rows (default setting) and tries to detect the type of data in the columns.

Some situations might occur where Power BI Desktop does not detect the correct data type. Where incorrect data types occur, you will experience performance issues.

Question 8

When are data detection problems worst?

Answer 8

Non-Database: You have a higher chance of getting data type errors when you are dealing with flat files, such as comma-separated values (.CSV) files and Excel workbooks (.XLSX), because data was entered manually into the worksheets and mistakes were made.
Databases: Conversely, in databases, the data types are predefined when tables or views are created.

Question 9

What are the implications of incorrect data types?

Answer 9

When Power BI does not detect the correct type various problems can arise:

Prevent certain calculations
Deriving hierarchies
Creating proper relationships with tables
Inability to create a date hierarchy (e.g. recognised as a list of dates)

Question 10

What are the best practice ways to evaluate columns?

Answer 10

Datatype
Evaluate column data types before loading data into a Power BI Data Model

Format data
Apply format in the column (e.g., currency, decimal etc)
Change the summarisation default

Date
However, it is a best practice to use a date table and turn off the auto date/time to get rid of the auto generated hierarchy.

Question 11

Why combine multiple tables into a single table?

Answer 11

There are numerous reasons why you might need to combine queries or tables together.

Data model: too many tables exist, making it difficult to navigate an overly-complicated data model.
Redundant tables: a) several tables have a similar role OR b) table has only a column or two that can fit into a different table.
Single source of truth: reports and dashboards are derived from one-source-of-truth
Custom Column: You want to use several columns from different tables in a custom column

Question 12

What are the two ways to combine tables?

Answer 12

Append (horizontal): add rows to the bottom of the table

Merge (vertical): adding columns to the side of the table

Question 13

How can you append tables?

Answer 13

Select the tables you want to append from “Available tables” and move to “Tables to append“. Can append queries as new table or as part of existing.

Question 14

How can you merge tables?

Answer 14

Select the tables you want to merge and the column that is matching between the tables. Then select one of the following options:
Left Outer - Displays all rows from the first table and only the matching rows from the second.
Full Outer - Displays all rows from both tables.
Inner - Displays the matched rows between the two tables.

Question 15

What is best practice when combining tables?

Answer 15

(1) Step 1: Edit/Format first
- Delete all extraneous columns
- Keep only the pertinent columns
- Rename so the different tables have the same named columns

(2) Step 2: Join tables

Question 16

What is profiling data in Power BI?

Answer 16

Profiling data is about evaluating data quality: determining anomalies, examining and developing the underlying data structures, and querying data statistics such as row counts, value distributions, minimum and maximum values, averages, and so on.

Question 17

Why is profiling data important?

Answer 17

This concept is important because it allows you to shape and organize the data by
 Remove outliers 
 Find anomalies 
 Check distributions
 Investigate specific data points

Question 18

Explain the different profiling tools?

Answer 18

Column Distribution
Distribution visualisation at the top of columns
Displays the counts of distinct and unique values

Column Quality
Gives the percentage of valid, error and empty values

Column Profile
Column profile gives you a more in-depth look into the statistics within the columns for the first 1,000 rows of data.
Count of rows can indicate whether the import is successful or not
Column statistics = a more in-depth look into the statistics within the columns for the first 1,000 rows of data including, summary statics, zero counts etc
The Value distribution = the count for each distinct value within the column

Question 19

What is a common troubleshoot with profile tools?

Answer 19

When you are not using the correct data type (e.g. numbers)

Question 20

What is advanced editor?

Answer 20

• Each time you shape data in Power Query, you create a step in the Power Query process.
• Each cleaning step that you made was likely created by using the graphical interface, but Power Query uses the M language behind the scenes.

These steps are available to read by using the Power Query Advanced Editor

Question 21

How is M code written?

Answer 21

• Each step is one or two lines of code
• M code is written top-down (e.g. later steps refer to earlier steps to the left of the equal sign)
• It is case sensitive
• Starts with let and ends with in

Question 22

What is the difference between DAX and M code?

Answer 22

DAX

• App = Power BI desktop
• Purpose = used to transform and create new columns within Power BI desktop

M-code (Mashup)

• App = language used by Power Query
• Purpose = (a) used to connect data (b) simplify, combine and transform data in Power Query Editor

Question 23

What are table transforms?

Answer 23

Transformation on whole tables, think about the column vs table icon in DAX

Question 24

Name the table transforms?

Answer 24

Promote or demote column headers
 Filter by row position 
 Filter by values 
 Choose or remove columns 
 Grouping or summarising rows
 Unpivot or Pivot
 Transpose 
 Reverse rows 
 Data types
 Dealing with errors 
 Working with duplicates

Question 25

What are column transforms?

Answer 25

Transforms values within columns

Question 26

Name the column transforms?

Answer 26

``` Fill values Sort columns Rename columns Move columns Replace Values Parse text Extract Text ```

Question 27

How do you record profile statistics?

Answer 27

- Column quality = copy quality metrics - Column distribution = copy distribution - Column profile = copy value distribution

Question 28

What is default profile behaviour and what is the alternative?

Answer 28

- To check the first 1000 rows (however this isn't a full reflection of data) - Change to entire dataset so you can see a more complete picture (however this could slow Power Query editor down)

Question 29

What does the column chart show in column profile?

Answer 29

- Shows shape of data in that column | - Is the distribution uniform

Question 30

What is table schema?

Answer 30

- Prefixes and suffixes associated with particular database tables

Question 31

Name some of the value distribution features?

Answer 31

- Can select (…) and group the values

Question 32

What does simplifying the data mean?

Answer 32

- Resolve inconsistencies - Unexpected or null values - Data quality issues - User-friendly values

Question 33

Name some complex query types?

Answer 33

Nested cells: - Errors - Lists - Records - Attributes - Tables Selecting any of these links adds another query step

Question 34

Why might Power Query use its autodetect feature?

Answer 34

- When the data source does not communicate the data type

Question 35

How can you disable auto-detect?

Answer 35

- Options & Settings > options > CURRENT FILE > Data Loaded > Type Detection

Question 36

Why might you disable auto-detect?

Answer 36

- Sometimes it can lead to incorrect formatting which has downstream implications - Replace errors otherwise Power Query may convert to text

Question 37

What are the drawbacks to data formatting in Power BI?

Answer 37

- Not all data types are available (e.g., percentage, currency etc) - Further formatting can occur in Data view within Power BI desktop

Question 38

What is a general rule of thumb when it comes to data formatting?

Answer 38

- If you are not going to perform mathematical calculations on it then it is not worth being numeric (e.g., Postcodes have numbers but will not be used in a calculations)

Question 39

Why might you see errors even if you have corrected the data type?

Answer 39

- Power Query caches output rather than updating immediately - Click on another query after data conversion to force changes

Question 40

Why is locale important?

Answer 40

- Change date format to match local area

Question 41

Explain the problem with incorrect locale?

Answer 41

- Incorrect locale can result in faulty data (e.g., 9/11 in US and 11/9 in UK) - Power BI cannot detect this nuance

Question 42

How might we use column quality?

Answer 42

- Allows us to check if the queried data has loaded correctly - Get an initial check on data quality - Nulls are not included in error count (e.g., null not recognised as text)

Question 43

Are column profile tools always reliable?

Answer 43

- No because it shows the first 1000 rows | - Right-Click and change to entire dataset

Question 44

What does the column distribution chart show?

Answer 44

- The shape of the data

Question 45

Name an advanced feature of the column distribution tool?

Answer 45

- Group values to profile different values

Question 46

How can you use replace values?

Answer 46

- Match entire cell: will only replace if the value is a complete match rather than just a part match - Replace using special characters: null is an example of special character

Question 47

How can you identify the root cause of query errors in Power Query?

Answer 47

- Checking nested contents (e.g., errors, lists, records and tables)

Question 48

How can you use keys in Power BI?

Answer 48

Can combine tables in several ways: - Combine tables (in Power Query or DAX) - Create relationships (in Power BI in Model view)

Question 49

Explain the similarities and differences of keys for joins and relationships?

Answer 49

Keys for joins - Not necessary to create composite key - Column data type important - Can use multiple columns Keys for relationships - More forgiving of data type - Only creates relationships on single pair of columns - May need to merge columns first if you have composite key

Question 50

Explain how to combine columns?

Answer 50

Two options - 1) Create new column - 2) Merge in place n.b. column selection order important (order of text follows order of column)

Question 51

How can you get an accurate row count?

Answer 51

- Data view will give an exact answer once loaded

Question 52

What is a common way to break queries?

Answer 52

- When you change query steps | - The last steps query the previous steps

Question 53

Explain what pivoting does?

Answer 53

- Creates a new column for each value

Question 54

Explain what unpivoting does?

Answer 54

- Selected columns are converted into 2 columns

Question 55

What does transpose do?

Answer 55

- Swaps the rows and columns

Question 56

Explain the features of adding columns?

Answer 56

- Column from examples - Power Query attempts to write a transformation sequence that reflects your actions - Custom column - type own M formula - Invoke custom function - invokes a custom function for every row of a table - Conditional column - specify if-then-else logic - Index column - creates a sequential column that’s starts and increments the value you specify - Duplicate column - copy of column you select

Question 57

What are the table transformations?

Answer 57

- Reduce or keep rows - Pivot or unpivot - Adding columns

Question 58

Name a troubleshoot with custom column?

Answer 58

- Creates a column without a data type so you have to specify the data type manually before loading to ensure it loads correctly

Question 59

What does the double-arrows in a header do for Lists?

Answer 59

Expand list - Expand to new rows: creates a new for each row inside the row's list - Extract values: keep row numbers the same and concatenate list rows into one cell with a separator you specify

Question 60

How can you preview a nested cell?

Answer 60

Select the cell with the hyperlink

Question 61

Name some nested function in M code?

Answer 61

- Braces = a list | - Double period = range

Question 62

Explain append?

Answer 62

- Joins when there are multiple matching columns | - Columns which don't line up with new table(s) will be filled with null values (table gets taller)

Question 63

Explain how to use merge?

Answer 63

- Need a set of keys or matching values | - Specify which rows of the first table should be combined with which rows of the second table

Question 64

Explain the different joins in merge? Give an example of how this works?

Answer 64

- Left outer = all from first match from second table - Right outer = all from second matching from first table - Full outer = all rows from both - Inner = only matching rows - Left anti = rows only in first - Right anti = rows only in second The merge joins are all about the a) columns that have matching values and b) the order in which you select the tables. So if there is 1112 in the first table and 1234 in the second table we will only see the additional columns for 1(3 times) and 2 (once) for a first outer join…Conversely a right outer join would be about which columns in the second match from columns in the first

Question 65

What does the double-arrows in a header do for Tables?

Answer 65

Expands the table - Expand: select the columns from the joined table that will be added to the current table (matching rows duplicated) - Aggregate: will aggregate rows and won't duplicate any rows…aggregating means you can apply statistical functions to the columns that have joined

Question 66

What is camel case?

Answer 66

When words are lowercase to uppercase….camelCase

Question 67

Why are naming conventions important?

Answer 67

- Report builders may want to browse data model fields and will need to have some intuitive understanding of what each field means (aim to make the fields understandable to a non-technical person),

Question 68

Why use Advanced Editor?

Answer 68

- Although you can use the pre-built transformation buttons in Power Query Editor there is a lot more flexibility when you write M code

Question 69

Explain the M code around the first initial query steps?

Answer 69

- Source = is the only step that is the same - Navigation = all navigation steps are grouped in Power Query - Dealing with spaces = when you have special characters (e.g., a space) for a command M uses quotation marks and a prefixes them with a hashtag (e.g., #"Changed Type")

Question 70

How can you see the M code for one query step at a time?

Answer 70

- View > Formula bar