Module 1
How Power Query thinks
Sources, previews and applied steps: why every transformation is a repeatable recipe.
You'll be able to
- Explain applied steps as a recipe that reruns on refresh
- Read the M behind a simple query
- Tell Excel and Power BI hosts apart
Lessons
- 1.Applied steps are a recipe, not a one-off editExplain how applied steps chain together and why that makes queries repeatable.
- 2.Step order mattersPut cleaning, filtering and aggregating steps in an order that gives correct results.
- 3.Power Query in Excel and Power BI: same engine, different hostsTell which behaviour belongs to the M language and which belongs to the host application.
- 4.Read the M behind the Applied StepsRead a generated query and say what each step does and which step it depends on.
Take the how power query thinks quiz (8 questions)
Exercises
- 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.
- beginner~7 minFree
Fix a query whose steps point at the wrong step (kite shop)
A step was renamed and the next step still refers to the old name. Repair the chain so the query runs.
- beginner~7 minPro
Fix a query whose steps point at the wrong step (bakery)
A step was renamed and the next step still refers to the old name. Repair the chain so the query runs.
- beginner~7 minPro
Fix a query whose steps point at the wrong step (bike rental)
A step was renamed and the next step still refers to the old name. Repair the chain so the query runs.
- beginner~8 minFree
Reorder steps so cleaning happens before grouping (kite shop)
The query groups regions before cleaning them, so " north" and "North" are counted separately. Fix the step order.
- beginner~8 minPro
Reorder steps so cleaning happens before grouping (bakery)
The query groups regions before cleaning them, so " north" and "North" are counted separately. Fix the step order.
- beginner~8 minPro
Reorder steps so cleaning happens before grouping (bike rental)
The query groups regions before cleaning them, so " north" and "North" are counted separately. Fix the step order.
- beginner~6 minFree
Remove a leftover step that cuts the data short (kite shop)
A Kept First Rows step left over from testing silently drops orders. Find it and remove it.
- beginner~6 minPro
Remove a leftover step that cuts the data short (bakery)
A Kept First Rows step left over from testing silently drops orders. Find it and remove it.
- beginner~6 minPro
Remove a leftover step that cuts the data short (bike rental)
A Kept First Rows step left over from testing silently drops orders. Find it and remove it.
- beginner~5 minFree
Return the last step, not an earlier one (kite shop)
The query builds a Line Total column, but the result doesn't show it: the `in` clause returns an earlier step.
- beginner~5 minPro
Return the last step, not an earlier one (bakery)
The query builds a Line Total column, but the result doesn't show it: the `in` clause returns an earlier step.
- beginner~5 minPro
Return the last step, not an earlier one (bike rental)
The query builds a Line Total column, but the result doesn't show it: the `in` clause returns an earlier step.
- intermediate~7 minFree
Fix a query that removes a column before using it (kite shop)
The query trims columns too early, so the Revenue step can't find UnitPrice. Reorder the steps.
- intermediate~7 minPro
Fix a query that removes a column before using it (bakery)
The query trims columns too early, so the Revenue step can't find UnitPrice. Reorder the steps.
- intermediate~7 minPro
Fix a query that removes a column before using it (bike rental)
The query trims columns too early, so the Revenue step can't find UnitPrice. Reorder the steps.
- beginner~6 minFree
Fix a Changed Type step that breaks two columns (kite shop)
An automatic type step turned customer names into errors and stripped the zeros from order numbers.
- beginner~6 minPro
Fix a Changed Type step that breaks two columns (bakery)
An automatic type step turned customer names into errors and stripped the zeros from order numbers.
- beginner~6 minPro
Fix a Changed Type step that breaks two columns (bike rental)
An automatic type step turned customer names into errors and stripped the zeros from order numbers.
- intermediate~6 minFree
Replace a hand-typed list of IDs with a rule (kite shop)
The query removes cancelled orders by listing this week's cancelled OrderIDs. Make it work every week.
- intermediate~6 minPro
Replace a hand-typed list of IDs with a rule (bakery)
The query removes cancelled orders by listing this week's cancelled OrderIDs. Make it work every week.
- intermediate~6 minPro
Replace a hand-typed list of IDs with a rule (bike rental)
The query removes cancelled orders by listing this week's cancelled OrderIDs. Make it work every week.
- intermediate~7 minFree
Fix a step that silently skips the cleaning step (kite shop)
The query runs, but its totals are split across " north" and "North": the Group step reads Source instead of the cleaned step.
- intermediate~7 minPro
Fix a step that silently skips the cleaning step (bakery)
The query runs, but its totals are split across " north" and "North": the Group step reads Source instead of the cleaned step.
- intermediate~7 minPro
Fix a step that silently skips the cleaning step (bike rental)
The query runs, but its totals are split across " north" and "North": the Group step reads Source instead of the cleaned step.