Prepare for a Power Query practical test or analyst interview
Career · By Tahir Nas · Examples run in our simulator · Published · Updated
What tests usually contain
| Task type | What's being checked | Practise with |
|---|---|---|
| Clean an export | Types, trimming, removing junk rows, culture | Clean a sales export (free) |
| Combine sources | Append vs merge, keys, unmatched rows | Merge and anti-join tasks |
| Summarise | Group By grain, correct aggregation, sort | Region summary task |
| Fix a broken query | Reading M, finding the failing step | Fix-the-step tasks |
| Explain | Clear reasoning, checks, M vs DAX boundaries | The interview question bank |
A 60-minute practice routine
- 5 min: read the brief twice. Write down the output grain (one row per what?) and the measures.
- 10 min: inspect the data. Headers, types, blanks, odd values, duplicates. Note them.
- 30 min: build the query in the order import → types → clean → filter → combine → summarise → sort.
- 10 min: verify. Control totals, row counts, unmatched keys.
- 5 min: write three sentences explaining the approach and one trade-off.
Always finish with a check
The single habit that separates strong candidates is showing that the answer is right. A two-line reconciliation is enough:
let
Raw = #table({"OrderID", "Status", "Amount"}, {{"A-1", "Complete", 20}, {"A-2", "Cancelled", 35}, {"A-3", "Complete", 12}, {"A-3", "Complete", 12}}),
Deduped = Table.Distinct(Raw),
Complete = Table.SelectRows(Deduped, each [Status] = "Complete")
in
#table({"Check", "Value"}, {
{"Raw rows", Table.RowCount(Raw)},
{"Exact duplicates removed", Table.RowCount(Raw) - Table.RowCount(Deduped)},
{"Cancelled rows excluded", Table.RowCount(Deduped) - Table.RowCount(Complete)},
{"Completed amount", List.Sum(Complete[Amount])}
})Result
| Check | Value |
|---|---|
| Raw rows | 4 |
| Exact duplicates removed | 1 |
| Cancelled rows excluded | 1 |
| Completed amount | 32 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Explaining your work
Use five parts: goal, data issues, steps, checks, trade-offs. Say why the order matters where it does (clean before grouping, filter before ranking). If a question touches the report layer, say where you'd use DAX instead; see M vs DAX.
Honesty beats bluffing
If you haven't used a feature (gateways, folding against a real database), say so and describe what you'd check. Never describe work experience you don't have. The timed assessments on this site are self-assessment practice under time pressure, not a hiring credential.
Practise it in the simulator
Each task opens the exact exercise in the browser workspace and is checked against fresh data, not just the example. Free tasks need no account.
- beginner~10 minFree
Clean and summarize a sales export
Trim messy region names, keep valid completed orders, remove an exact duplicate and total revenue by region.
- intermediate~8 minPro
Find orders from unknown customers (kite shop)
Use a left anti join to list orders whose customer isn't in the customer master.
- intermediate~8 minPro
Summarise orders by region (kite shop)
Group by Region with order count, total quantity and average unit price, sorted by region.
Related pages
Lessons
Sources
Microsoft's official documentation for the features and functions used on this page.
- What is Power Query? (Concept, Microsoft Learn)
- Best practices when working with Power Query (Concept, Microsoft Learn)
- Table.Distinct (Function reference, Microsoft Learn)
- Table.SelectRows (Function reference, Microsoft Learn)
- Table.RowCount (Function reference, Microsoft Learn)
- List.Sum (Function reference, Microsoft Learn)
Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.