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.

Data Preparation in Power BI

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.

Data Preparation in Power BI

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 PowerBI interface, showing the Transform Data icon which opens the Power Query editor when clicked.

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 Preparation in Power BI

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

Data Preparation in Power BI

Basic structural transformation of data

  • Promote headers
  • Reorder columns
  • Sort columns
  • Remove columns/rows
  • Rename columns
Data Preparation in Power BI

Let's practice!

Data Preparation in Power BI

Preparing Video For Download...