Module 19
Refresh robustness and performance concepts
Writing queries that survive new data, and what query folding and privacy levels mean.
You'll be able to
- Avoid brittle steps
- Explain query folding (concept)
- Explain privacy levels (concept)
Lessons
- 1.Dynamic steps that survive new columnsReplace hard-coded column lists with rules that apply to future columns.
- 2.Query folding (concept)Explain query folding and recognise steps that tend to break it.
- 3.Privacy levels and the formula firewall (concept)Explain privacy levels and recognise formula firewall errors.
- 4.Refresh in Excel, Power BI Desktop and the service (concept)Anticipate why a query that refreshes on your machine fails elsewhere, and add checks that report it.
Take the refresh robustness and performance concepts quiz (8 questions)
Exercises
- intermediate~9 minPro
Clean every column name, including future ones (survey)
Trim and capitalise all column headers with one dynamic step instead of renaming each column by hand.
- intermediate~9 minPro
Clean every column name, including future ones (timesheet)
Trim and capitalise all column headers with one dynamic step instead of renaming each column by hand.
- advanced~10 minPro
Set types without failing when an optional column is missing (kite shop)
A Changed Type step that lists Discount breaks when an export has no Discount column. Type only the columns that are present.
- advanced~10 minPro
Set types without failing when an optional column is missing (bakery)
A Changed Type step that lists Discount breaks when an export has no Discount column. Type only the columns that are present.
- advanced~10 minPro
Set types without failing when an optional column is missing (bike rental)
A Changed Type step that lists Discount breaks when an export has no Discount column. Type only the columns that are present.
- intermediate~9 minPro
Fix a query that names columns by position (kite shop)
The starter skips the header line and renames Column1…Column4. When the export's column order changes, values land in the wrong columns.
- intermediate~9 minPro
Fix a query that names columns by position (bakery)
The starter skips the header line and renames Column1…Column4. When the export's column order changes, values land in the wrong columns.
- intermediate~9 minPro
Fix a query that names columns by position (bike rental)
The starter skips the header line and renames Column1…Column4. When the export's column order changes, values land in the wrong columns.
- intermediate~8 minPro
Keep the last seven days of data, relative to the data (kite shop)
Keep orders from the 7 days ending on the latest OrderDate in the export, so the query rolls forward on every refresh.
- intermediate~8 minPro
Keep the last seven days of data, relative to the data (bakery)
Keep orders from the 7 days ending on the latest OrderDate in the export, so the query rolls forward on every refresh.
- intermediate~8 minPro
Keep the last seven days of data, relative to the data (bike rental)
Keep orders from the 7 days ending on the latest OrderDate in the export, so the query rolls forward on every refresh.
- intermediate~7 minPro
Accept both old and new names for a column (kite shop)
Older exports call the column Quantity, newer ones Qty. Always end up with Qty, whichever arrives.
- intermediate~7 minPro
Accept both old and new names for a column (bakery)
Older exports call the column Quantity, newer ones Qty. Always end up with Qty, whichever arrives.
- intermediate~7 minPro
Accept both old and new names for a column (bike rental)
Older exports call the column Quantity, newer ones Qty. Always end up with Qty, whichever arrives.
- advanced~10 minPro
Switch the source query with a parameter (kite shop)
The same report runs for North or South. A Settings row says which; pick the matching source query without editing the steps.
- advanced~10 minPro
Switch the source query with a parameter (bakery)
The same report runs for North or South. A Settings row says which; pick the matching source query without editing the steps.
- advanced~10 minPro
Switch the source query with a parameter (bike rental)
The same report runs for North or South. A Settings row says which; pick the matching source query without editing the steps.
- advanced~13 minPro
Fill in missing store–month combinations with zero (kiosks)
Stores with no sales in a month have no row at all, so charts skip them. Produce every store × month pair, with 0 where there were no sales.
- advanced~13 minPro
Fill in missing store–month combinations with zero (pop ups)
Stores with no sales in a month have no row at all, so charts skip them. Produce every store × month pair, with 0 where there were no sales.
- advanced~11 minPro
Replace sort-then-remove-duplicates with a deterministic rule (subscriptions)
The starter sorts newest first and removes duplicates to keep each account's latest status. Power Query doesn't guarantee which duplicate survives; make it explicit.
- advanced~11 minPro
Replace sort-then-remove-duplicates with a deterministic rule (support plans)
The starter sorts newest first and removes duplicates to keep each account's latest status. Power Query doesn't guarantee which duplicate survives; make it explicit.