Introduction to Power Query

Data Preparation in Power BI

Khaled Choucri

Strategic Analyst - Trilogy

Why prepare data?

Illustration showing "garbage data" being used and resulting in "garbage data" coming out of any analysis. This results in having more questions than answers about the data.

What is clean data?

Free from missing values
Typos/data entry errors are corrected
No duplicated data
Irrelevant data is excluded
Outliers are dealt with
Correct data types for all columns
Column & table names are short and descriptive

A table showing some examples of dirty data.

How does data preparation work in Power Query?

To load & transform data, we use the Power Query interface in Power BI
Transformation steps are added sequentially
Steps are saved in a "recipe" called "Applied Steps" that executes every time your data is loaded/refreshed
To apply the transformations, we choose "Close & Apply" in the Power Query editor

A screenshot of the Applied Steps pane in the Power Query Editor, which shows the series of transformation steps that have been applied to your data.

A screenshot of the Close & Apply icon in Power Query, which closes the Power Query editor and applies the transformation steps to the data so we can analyze it in PowerBI.

Data types in Power Query

Numerical Data (Decimal / Whole Number / Percentage)
- 123, 45.23, 23%
Date / Time Data
- 01/01/2022, 09:30 AM
Text Data
- ABC
Logical Data
- TRUE
Binary Data
- RGF0YWNhbXA=

Power Query will auto detect data types by default

Basic structural transformation of data

Promote headers
Reorder columns
Sort columns
Remove columns/rows
Rename columns

Let's practice!

Data Preparation in Power BI