Power QueryExercises

Explain your query in an interview

Present a transformation clearly, including how you know it's correct.

~10 min read · intermediate · Module 20: Projects, debugging and interviews

By Tahir Nas · Examples run in our simulator · Updated

Before this: End-to-end project: from raw export to monthly report

Practical interview tasks rarely fail on syntax. They fail on explanation: the candidate built something, but can't say why each step is there or how they know the answer is right. A five-part structure keeps answers short and complete.

Goal, issues, steps, checks, trade-offs

  1. Goal: restate the question and the grain. 'Completed sales by region and month, one row per region-month.'
  2. Data issues: what you found before building. 'Banner row, German decimals, inconsistent region case, cancelled rows.'
  3. Steps: the transformation in plain words, in order, with the reason for any non-obvious order.
  4. Checks: how you verified it. Control totals, row counts, unmatched keys.
  5. Trade-offs: what you'd change with more time or different requirements.

Show a check, not just a result

A short reconciliation is the most convincing thing you can show. Compare input and output on the measures that matter:

let
    Input = #table({"OrderID", "Region", "Amount", "Status"}, {
        {"S-1", " north", 19.5, "Complete"},
        {"S-2", "North", 6, "Cancelled"},
        {"S-3", "SOUTH", 39, "Complete"}
    }),
    Complete = Table.SelectRows(Input, each [Status] = "Complete"),
    Output = Table.Group(Table.TransformColumns(Complete, {{"Region", each Text.Proper(Text.Trim(_))}}), {"Region"}, {{"Sales", each List.Sum([Amount]), type number}})
in
    #table({"Check", "Input", "Output", "OK"}, {
        {"Completed amount", List.Sum(Complete[Amount]), List.Sum(Output[Sales]), List.Sum(Complete[Amount]) = List.Sum(Output[Sales])},
        {"Regions", null, Table.RowCount(Output), Table.RowCount(Output) = 2}
    })

Result

CheckInputOutputOK
Completed amount58.558.5true
Regionsnull2true

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Where M ends and DAX begins

Belongs in Power Query (M)Belongs in DAX
Cleaning text, fixing types and culturesMeasures that respond to slicers and filters
Reshaping: unpivot, append, merge for lookup columnsRatios and percentages across the current filter context
Removing rows that should never be analysedTime intelligence: year-to-date, same period last year
Row-level calculated columns that never change with filtersRankings and totals that change with the report's selection

A good one-line rule for interviews: shape and clean in Power Query; aggregate in DAX when the answer depends on what the user has selected.

Be honest about what you know

If you haven't used a feature (query folding against a real database, a gateway), say so and explain what you'd check. Interviewers value accurate limits more than confident guesses. Don't describe work you haven't done; describe how you'd approach it.

Where to find this in Excel

Excel for Windows
Power Query editor > View > Advanced Editor
What you see
The whole query appears as M with one named step per line, which is the easiest view to talk someone through.

Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Use Power Query to transform data (the Power Query editor) (Microsoft Learn).

Common mistakes

  • Describing clicks ('then I pressed Group By') instead of intent ('one row per region-month').
  • Presenting a result without any check that it's correct.
  • Claiming experience with features you've only read about.

Excel, Power BI and the simulator

  • Interview tasks may use Excel or Power BI; the explanation structure is the same, and the M is identical.
  • If the task allows DAX, say explicitly which parts you'd move there and why.
  • The simulator's assessments are self-assessment practice, not a proctored or hiring credential.
Functions in this lesson:Table.RowCountList.Sum

Next step

Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.

Take the Debugging and interview quiz