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.

Why query folding is worth understanding, not just query-writing

The performance difference query folding makes is large enough that it's worth actively checking, not just hoping for. In Power Query's editor, right-clicking a step and looking for a greyed-out "View Native Query" option is the quickest way to confirm whether a transformation is still folding back to the source — if it's available, the step is generating a native query the source database executes; if it's greyed out, everything from that point onward runs client-side in Power Query's own engine, pulling the full unfiltered dataset across the network first. The practical implication for query design: put filtering, column removal, and aggregation steps as early as possible in the applied-steps sequence, before any custom M function or unusual transformation that's likely to break folding — reordering steps that are logically independent of each other can be the difference between a refresh that takes seconds and one that takes many minutes against a large source table.

Frequently asked questions

Is Power Query the same product in Excel, Power BI, and Fabric, or just similar? It's genuinely the same underlying engine and M language across all of them — a query built in Excel can be copied (via the Advanced Editor's M code) into Power BI or Fabric with little to no modification, which is exactly what makes the skill transferable rather than needing to be relearned per tool.

Can Power Query handle real-time or streaming data? No — it's fundamentally a batch ETL tool that refreshes on a schedule or on demand, not a streaming engine; scenarios needing genuinely real-time data (a live dashboard updating second-by-second) need a different tool in the stack, such as a Power BI streaming dataset or an Azure Stream Analytics pipeline, with Power Query remaining the right choice for everything that can tolerate a refresh interval.