Shaping data with Power Query and M

Every Power BI report has two engines. Power Query prepares the data; the model (DAX) analyses it. Beginners try to fix dirty data with DAX and end up with fragile reports. The rule is simple: shape it in Power Query, measure it in DAX.

Our running example is Rajlaxmi Distributors, an FMCG distributor in Nagpur that supplies soaps, tea and biscuits to kirana stores across Maharashtra. Their data arrives as one Excel file per depot, plus a product master from the ERP.

The M language behind the ribbon

Every click in the Power Query editor appends a step to a script written in M. Open Home → Advanced Editor to see it:

let
    Source = Excel.Workbook(File.Contents("D:\Data\Nagpur.xlsx"), null, true),
    Sales  = Source{[Item = "Sales", Kind = "Sheet"]}[Data],
    Headers = Table.PromoteHeaders(Sales, [PromoteAllScalars = true]),
    Typed  = Table.TransformColumnTypes(
                Headers,
                {{"InvoiceDate", type date}, {"Qty", Int64.Type}, {"NetAmount", Currency.Type}}
             ),
    Clean  = Table.SelectRows(Typed, each [NetAmount] <> null and [Qty] > 0)
in
    Clean

M is case-sensitive, and each step is just a named variable feeding the next. You rarely write it from scratch — but reading it lets you fix a broken step in seconds instead of redoing ten clicks.

Unpivot: the single most useful transform

Depot managers send data wide: one column per month. Analytics needs it tall.

Store	Apr	May	Jun
Kirana Bazaar, Nagpur	48200	51350	44900

Select the Store column, then Transform → Unpivot Other Columns. You get three columns — Store, Attribute, Value — which you rename to Store, Month, Sales. Always use Unpivot Other Columns rather than selecting the month columns: when July arrives, the query keeps working instead of erroring.

Merge vs Append

These two are constantly confused:

Operation	SQL equivalent	Use when
Merge Queries	JOIN	You want to add columns — pull Brand and MRP from the product master onto sales rows
Append Queries	UNION ALL	You want to add rows — stack Nagpur, Pune and Nashik depot tables into one Sales table

For merges, choose the join kind deliberately. A Left Outer merge keeps every sale even when a product code is missing from the master; after expanding, filter for nulls to find the bad codes instead of silently losing revenue.

To append a whole folder of depot files, use Get Data → Folder, then Table.Combine via the generated sample query — new files dropped in the folder are picked up on the next refresh with no editing.

Query folding

When the source is a database, Power Query tries to translate your steps back into a single SQL statement. This is query folding, and it is the difference between a 20-second refresh and a 20-minute one. Right-click a step and check View Native Query: if it is greyed out, folding stopped there. Filters, column removal, renames, type changes and merges usually fold; custom M functions and Table.Buffer usually break it. Put the folding steps first.

Common mistakes

• Hard-coding a local path like D:\Data — use a parameter so the same file works on a colleague's machine.
• Leaving Changed Type as the first step after a folder import; a new column order will break it.
• Doing lookups with DAX LOOKUPVALUE when a Merge in Power Query is cheaper and refresh-time.
• Loading staging queries into the model. Right-click them and untick Enable load.