Concept
For this track use sales/sales_data.csv as the sales source and sales_targets.csv separately; do not combine both schemas with From Folder. The supplied sales file already has StoreName/Product/Region rather than StoreID/ProductID. Check blank labels, unique OrderIDs, valid dates and revenue reconciliation. The dimension-key example below applies only when you bring separate raw dimension tables.
1. Power Query as the RAW layer
Instead of pasting exports, let queries load RAW (Analysis track, Module 3):
| Query | Source | Load |
|---|---|---|
stg_Sales |
folder of monthly CSVs, cleaned | Connection only |
dim_Stores, dim_Products |
small lookup files | Connection only / Data Model |
fact_Sales |
stg_Sales + merges | Table tblSales on RAW_Sales (or Data Model) |
fact_Targets |
targets file | Table tblTargets |
chk_Quality |
row counts, unknown codes, blank dates | small Table on README |
Staging keeps cleaning in one place; every output references it.
2. A data-quality check query
Build chk_Quality that returns one row per check:
| Check | Value | Expected |
|---|---|---|
| Rows in fact_Sales | 50,000 | > 0 and ≥ last month |
| Unknown StoreIDs | 0 | 0 |
| Blank dates | 0 | 0 |
| Latest order date | 31-Mar-2026 | end of last month |
Show it on README (and a small "Data as of 31-Mar-2026" note on DASH). A dashboard that shows wrong data confidently is worse than no dashboard.
3. Refresh order
Refresh All refreshes connections first, then pivots — but with background refresh on, pivots can refresh before slow queries finish. For dashboard queries:
- Query Properties → untick Enable background refresh.
- Keep pivot caches refreshing after queries (default when background is off).
- If you use VBA:
ThisWorkbook.RefreshAllthenApplication.CalculateUntilAsyncQueriesDone.
4. When to refresh
| Strategy | Setting | Use when |
|---|---|---|
| On open | Query Properties → Refresh data when opening the file | viewers open a shared file and must see latest data |
| Manual | button / Refresh All by the owner, then save and share | data updates monthly; viewers get a finished file or PDF |
| Timed | Refresh every N minutes | live operational screens while the file is open |
| Scheduled (cloud) | Power Automate + Office Scripts, or Power BI | nobody should open Excel to refresh (Automation track) |
For most monthly business dashboards: manual refresh by the owner, save, share — viewers then don't need access to the source folders.
5. Sources and credentials
- Keep source paths in a parameter cell (
inp_DataFolder). - Files on SharePoint/OneDrive: use SharePoint Folder connectors so paths work for the whole team.
- Viewers without access to the source get refresh errors if "refresh on open" is on — turn it off for shared copies.
6. A "Refresh" checklist on README
- Copy new month's file into the data folder.
- Data → Refresh All.
- Check
chk_Quality— all green. - Check total revenue against the accounting system for the month.
- Clear slicer filters, set DASH as active sheet, save as
Sales_Dashboard_2026-03.xlsx(or export PDF).
Common mistakes
Background refresh causing half-refreshed dashboards. Refresh-on-open in files sent to people without source access. No "data as of" date on the dashboard. Skipping the quality check.