3.3.1. Power Query (M) Transformations

💡 First Principle: Power Query (M) is a functional language optimized for data transformation, powering both Dataflow Gen2 and Power BI data preparation. It excels at visual, no-code transformations but struggles with massive datasets or complex logic.

Common M Transformations

• Merge vs. Append: Merge is a join that adds columns (a Left Outer merge on CustomerID adds customer names to every order); Append stacks rows from tables with the same schema.
• Query folding: Power Query translates steps into one native source query. Filters fold to WHERE, removing columns folds to a narrower SELECT, and Group By folds to GROUP BY, so the work runs in the source database. Table.Buffer, index columns and custom functions the connector can't translate break folding, and every later step then runs in the mashup engine.
• Staging: with staging enabled, Dataflow Gen2 lands query data in its staging lakehouse and runs downstream transformations on Fabric (warehouse) compute instead of the gateway machine. That is the usual fix for slow gateway-bound refreshes.
• Incremental refresh: reloads only buckets whose DateTime column shows changes instead of the whole table on every run.
• Gen1 vs. Gen2: Gen1 keeps its output in dataflow storage for Power BI semantic models to import. Gen2 adds data destinations (lakehouse, warehouse, KQL database and others), staging and pipeline integration, and is the choice for Fabric data engineering.