Excel weekly dashboards at scale

Excel weekly dashboards at scale

PURPOSE

Designs refreshable Excel dashboards (Power Query + structured tables + validation + pivot reporting).

WHEN TO USE

• TRIGGERS:
  • Build me a Power Query pipeline for this file so it refreshes weekly with no manual steps.
  • Turn this into a structured table with validation lists and clean data entry rules.
  • Create a pivot-driven weekly dashboard with slicers for year and ISO week.
  • Fix this Excel model so refresh does not break when new columns appear.
  • Design a reusable KPI pack that updates from a folder of CSVs.
• DO NOT USE WHEN…
  • You need advanced forecasting/valuation modeling (this skill is for repeatable reporting pipelines).
  • You need a BI tool build (Power BI/Tableau) rather than Excel.
  • You need web scraping as the primary ingestion method.

INPUTS

• REQUIRED:
  • Source data file(s): CSV, XLSX, DOCX-exported tables, or PDF-exported tables (provided by user).
  • Definition of ‘week’ (ISO week preferred) and the KPI fields required.
• OPTIONAL:
  • Data dictionary / column definitions.
  • Known “bad data” patterns to validate (e.g., blank PayNumber, invalid dates).
  • Existing workbook to refactor.
• EXAMPLES:
  • Folder of weekly CSV exports: exports/2026-W02/*.csv
  • Single XLSX dump with changing columns month to month

OUTPUTS

• If asked for plan only (default): a step-by-step build plan + Power Query steps + sheet layout + validation rules.
• If explicitly asked to generate artifacts:
  • workbook_spec.md (workbook structure and named tables)
  • power_query_steps.pq (M code template)
  • refresh-checklist.md (from assets/) Success = refresh works after adding a new week’s files without manual edits, and validation catches bad rows.

WORKFLOW

1. Identify source type(s) (CSV/XLSX/DOCX/PDF-export) and the stable business keys (e.g., PayNumber).