Power Query primer

Power Query is the data import and transformation engine that appears throughout Microsoft's data and analytics tools — Excel, Power BI, Power Automate dataflows, Microsoft Fabric, Dataverse, and SQL Server Analysis Services. Once you know it, the same skill applies across the stack.

What Power Query does

Power Query is fundamentally an ETL (Extract, Transform, Load) tool:

• Connect to a data source — Excel files, CSV, SQL, SharePoint lists, Web APIs, Azure Data Lake, hundreds more.
• Transform the data — remove columns, filter rows, group, pivot, unpivot, merge, append, type conversions, custom calculations.
• Load the transformed data into the destination — Excel sheet, Power BI model, Dataflow, etc.

Every step you take in the UI is recorded as a step in the Applied Steps pane, which you can reorder, edit, or remove. Steps are stored as expressions in the M language — Power Query's functional language.

The M language

The UI generates M behind the scenes, but learning a little M unlocks much more:

let
    Source = Excel.Workbook(File.Contents("C:\sales.xlsx"), null, true),
    Sales = Source{[Item="Sales",Kind="Table"]}[Data],
    Filtered = Table.SelectRows(Sales, each [Region] = "EMEA"),
    Grouped = Table.Group(Filtered, "Country", {{"TotalRevenue", each List.Sum([Revenue]), type number}})
in
    Grouped

M is functional and lazy — operations describe a transformation pipeline, not imperative steps. The engine optimises and pushes work down to the source when possible ("query folding").

Query folding

For SQL, OData, and many other sources, Power Query can push transformations back to the source server — generating a SELECT with WHERE clauses, joins, and aggregations rather than fetching everything and filtering client-side. This is query folding. When it works, performance is dramatically better.

Steps that don't fold (custom M functions, certain transformations) break the chain, so order matters.

Where you'll meet Power Query

• Excel → Data → Get Data — most common entry point.
• Power BI Desktop → Transform Data — same engine, deeper modelling.
• Power BI / Fabric Dataflows — Power Query running in the cloud, shared across reports.
• Power Automate dataflows — automating data shaping into Dataverse.
• Microsoft Fabric — Dataflow Gen2 and Pipelines surface Power Query for warehouse-class ETL.

When to use what

• For one-time data wrangling in Excel — Power Query in Excel is perfect.
• For Power BI report sources — Power Query in Power BI Desktop or, for shared scenarios, Dataflows.
• For enterprise data engineering at scale — Power Query in Microsoft Fabric Dataflow Gen2 or hand off to Spark / SQL.

Once you've internalised the Applied Steps mental model and the M language, Power Query becomes one of the most useful skills in the Microsoft data stack.