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

Identify source type(s) (CSV/XLSX/DOCX/PDF-export) and the stable business keys (e.g., PayNumber). Define the canonical table schema: required columns, types, allowed values, and “unknown” handling. Design ingestion with Power Query: Prefer Folder ingest + combine, with defensive “missing column” handling. Normalize column names (trim, case, collapse spaces). Design cleansing & validation: Create a Data_Staging query (raw-normalized) and Data_Clean query (validated). Add validation columns (e.g., IsValidPayNumber, IsValidDate, IssueReason). Build reporting layer: Pivot table(s) off Data_Clean Slicers: Year, ISOWeek; plus operational dimensions Add a “Refresh Status” sheet: last refresh timestamp, row counts, query error flags, latest week present STOP AND ASK THE USER if: required KPIs/columns are unspecified, the source files don’t include any stable key, week definition/timezone rules are unclear, PDF/DOCX tables are not reliably extractable without a provided export.

OUTPUT FORMAT

• When producing a plan, use this template:
• WORKBOOK PLAN
• Sheets:
• - Data_Staging (query output)
• - Data_Clean (query output + validation flags)
• - Dashboard (pivots/charts)
• - Refresh_Status (counts + health checks)
• Canonical Schema:
• - <Column>: <Type> | Required? | Validation
• Power Query:
• - Query 1: Ingest_<name> (Folder/File)
• - Query 2: Clean_<name>
• - Key transforms: <bullets>
• Validation rules:
• - <rule> -> <action>
• Pivot design:
• - Rows/Columns/Values
• - Slicers
• If asked for artifacts, also output:
• assets/power-query-folder-ingest-template.pq (adapted)
• assets/refresh-checklist.md

SAFETY & EDGE CASES

Read-only by default: provide a plan + snippets unless the user explicitly requests file generation. Never delete or overwrite user files; propose new filenames for outputs. Prefer “no silent failure”: include row-count checks and visible error flags. For PDF/DOCX sources, require user-provided exported tables (CSV/XLSX) or clearly mark extraction risk.

EXAMPLES

Input: “Folder of weekly CSVs with PayNumber/Name/Date.” Output: Folder-ingest PQ template + schema + Refresh Status checks + pivot dashboard plan. Input: “Refresh breaks when new columns appear.” Output: Defensive missing-column logic + column normalization + typed schema plan.