Introduction to Power Query Editor

Power Query Editor is the data transformation engine inside Power BI Desktop. It allows you to clean, reshape, and prepare your data before it enters the data model.

What is Power Query?

• A data connection and transformation tool built into Power BI, Excel, and other Microsoft products • Uses the M language (Power Query Formula Language) behind the scenes • Provides a no-code / low-code visual interface for data transformations • All transformations are recorded as steps and can be replayed automatically on data refresh

Opening Power Query Editor

Methods to Open:

1. Home → Transform Data (opens all loaded queries)
2. While importing data → Click Transform Data instead of Load
3. From the Data View → Right-click a table → Edit Query

Power Query Editor Interface

Key Concepts

Applied Steps: • Every action you perform is recorded as a step • Steps are applied sequentially (top to bottom) • You can click any step to see the data at that point • Steps can be renamed, deleted, moved, or edited

M Language: • The underlying language that Power Query generates • Each step produces an M expression • Advanced users can write custom M code in the formula bar or Advanced Editor

Example M Code:

let
    Source = Excel.Workbook(File.Contents("C:\Data\Sales.xlsx")),
    Sheet1 = Source{[Name="Sheet1"]}[Data],
    PromotedHeaders = Table.PromoteHeaders(Sheet1),
    FilteredRows = Table.SelectRows(PromotedHeaders, each [Amount] > 100)
in
    FilteredRows

Power Query vs DAX