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
- Goal: restate the question and the grain. 'Completed sales by region and month, one row per region-month.'
- Data issues: what you found before building. 'Banner row, German decimals, inconsistent region case, cancelled rows.'
- Steps: the transformation in plain words, in order, with the reason for any non-obvious order.
- Checks: how you verified it. Control totals, row counts, unmatched keys.
- 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
| Check | Input | Output | OK |
|---|---|---|---|
| Completed amount | 58.5 | 58.5 | true |
| Regions | null | 2 | true |
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 cultures | Measures that respond to slicers and filters |
| Reshaping: unpivot, append, merge for lookup columns | Ratios and percentages across the current filter context |
| Removing rows that should never be analysed | Time intelligence: year-to-date, same period last year |
| Row-level calculated columns that never change with filters | Rankings 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.
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