PowerQuery_Course_Notes.pdf

The below content are my notes from the course Master Excel Power Query by Leila.

Course Length: 16.5 hours

The course does cover power query in depth with a lot of use case scenarios. That helps you to connect with your business and transformation problems. If you are using Power BI, Power Query is an absolutely necessary tool that you should learn because it does almost everything when it comes to data cleaning and transformation..

Loved the course content and the flow!!

A book that I referred during the course and have in handy for Power Query and M Language is

M Is for Data Monkey

What is a Proper DataSet:

A top row has unique column headers. No gaps or empty columns. No Subtotals in the rows. Datatypes are specified.

Why Power Query?

File Size, Keep Source Data separate from Report, Data can be transformed. You can combine data from other file (Data Model) or Merge multiple files. You also get audit trails of the steps that you did. Automate entire ETL process.

<aside> 💡 Remember power query is case and type sensitive

</aside>

ETL:

You cannot do complex querying but all data cleaning steps can be done in Power Query.

Transform vs Add Column:

Refresh Data:

The transform changes the data itself while add column helps in creating a new column with corrected data retaining the original column.

If the Load To is disabled after loading, you can go to the spreadsheet, connection, right click→ Load To

Column Profiling and Checking Data Quality:

• Always Profile column for the entire dataset, check distribution and then revert back to only 1000 rows.
• You can also check column quality to check for Valid/Errors/Empty.
• Transform→Count Rows. Checks the number of rows.
• Transform→Statistics. Min, Median, Max etc
• On the transformation steps, if you want to add notes, you can add under properties steps.

You can refresh from the connection or the pivot. You can set autorefresh from Data→Properties.

Merge Queries:

Just like vlookup helps with looking up with second table and match with the first table.

Data Types:

Decimal Number, Currency, Percentage, DateTime, Date, Time, Duration, Text, True/False, Binary, Using Locale.

Data Types Vs Formatting in Excel:

If you for format a datapoint in excel, the old value is still used for calculation while in -power bi, the data is hard changed.

• Shortcuts

If you want custom load,

• File-Query Setting
• Always use "Connection only"

Error Handling:

When there is an error, a new query will be generated with details of errors and you can choose to remove the errors, remove rows, replace error values.