{"skill":{"slug":"excel-weekly-dashboard","displayName":"Excel weekly dashboards at scale","summary":"Designs refreshable Excel dashboards (Power Query + structured tables + validation + pivot reporting). Use when you need a repeatable weekly KPI workbook that updates from files with minimal manual work.","description":"---\nname: excel-weekly-dashboard\ndescription: Designs refreshable Excel dashboards (Power Query + structured tables + validation + pivot reporting). Use when you need a repeatable weekly KPI workbook that updates from files with minimal manual work.\n---\n\n# Excel weekly dashboards at scale\n\n## PURPOSE\nDesigns refreshable Excel dashboards (Power Query + structured tables + validation + pivot reporting).\n\n## WHEN TO USE\n- TRIGGERS:\n  - Build me a Power Query pipeline for this file so it refreshes weekly with no manual steps.\n  - Turn this into a structured table with validation lists and clean data entry rules.\n  - Create a pivot-driven weekly dashboard with slicers for year and ISO week.\n  - Fix this Excel model so refresh does not break when new columns appear.\n  - Design a reusable KPI pack that updates from a folder of CSVs.\n- DO NOT USE WHEN…\n  - You need advanced forecasting/valuation modeling (this skill is for repeatable reporting pipelines).\n  - You need a BI tool build (Power BI/Tableau) rather than Excel.\n  - You need web scraping as the primary ingestion method.\n\n## INPUTS\n- REQUIRED:\n  - Source data file(s): CSV, XLSX, DOCX-exported tables, or PDF-exported tables (provided by user).\n  - Definition of ‘week’ (ISO week preferred) and the KPI fields required.\n- OPTIONAL:\n  - Data dictionary / column definitions.\n  - Known “bad data” patterns to validate (e.g., blank PayNumber, invalid dates).\n  - Existing workbook to refactor.\n- EXAMPLES:\n  - Folder of weekly CSV exports: `exports/2026-W02/*.csv`\n  - Single XLSX dump with changing columns month to month\n\n## OUTPUTS\n- If asked for **plan only (default)**: a step-by-step build plan + Power Query steps + sheet layout + validation rules.\n- If explicitly asked to **generate artifacts**:\n  - `workbook_spec.md` (workbook structure and named tables)\n  - `power_query_steps.pq` (M code template)\n  - `refresh-checklist.md` (from `assets/`)\nSuccess = refresh works after adding a new week’s files without manual edits, and validation catches bad rows.\n\n\n## WORKFLOW\n1. Identify source type(s) (CSV/XLSX/DOCX/PDF-export) and the stable business keys (e.g., PayNumber).\n2. Define the canonical table schema:\n   - required columns, types, allowed values, and “unknown” handling.\n3. Design ingestion with Power Query:\n   - Prefer **Folder ingest** + combine, with defensive “missing column” handling.\n   - Normalize column names (trim, case, collapse spaces).\n4. Design cleansing & validation:\n   - Create a **Data_Staging** query (raw-normalized) and **Data_Clean** query (validated).\n   - Add validation columns (e.g., `IsValidPayNumber`, `IsValidDate`, `IssueReason`).\n5. Build reporting layer:\n   - Pivot table(s) off **Data_Clean**\n   - Slicers: Year, ISOWeek; plus operational dimensions\n6. Add a “Refresh Status” sheet:\n   - last refresh timestamp, row counts, query error flags, latest week present\n7. STOP AND ASK THE USER if:\n   - required KPIs/columns are unspecified,\n   - the source files don’t include any stable key,\n   - week definition/timezone rules are unclear,\n   - PDF/DOCX tables are not reliably extractable without a provided export.\n\n\n## OUTPUT FORMAT\nWhen producing a **plan**, use this template:\n\n```text\nWORKBOOK PLAN\n- Sheets:\n  - Data_Staging (query output)\n  - Data_Clean (query output + validation flags)\n  - Dashboard (pivots/charts)\n  - Refresh_Status (counts + health checks)\n- Canonical Schema:\n  - <Column>: <Type> | Required? | Validation\n- Power Query:\n  - Query 1: Ingest_<name> (Folder/File)\n  - Query 2: Clean_<name>\n  - Key transforms: <bullets>\n- Validation rules:\n  - <rule> -> <action>\n- Pivot design:\n  - Rows/Columns/Values\n  - Slicers\n```\n\nIf asked for artifacts, also output:\n- `assets/power-query-folder-ingest-template.pq` (adapted)\n- `assets/refresh-checklist.md`\n\n\n## SAFETY & EDGE CASES\n- Read-only by default: provide a plan + snippets unless the user explicitly requests file generation.\n- Never delete or overwrite user files; propose new filenames for outputs.\n- Prefer “no silent failure”: include row-count checks and visible error flags.\n- For PDF/DOCX sources, require user-provided exported tables (CSV/XLSX) or clearly mark extraction risk.\n\n\n## EXAMPLES\n- Input: “Folder of weekly CSVs with PayNumber/Name/Date.”  \n  Output: Folder-ingest PQ template + schema + Refresh Status checks + pivot dashboard plan.\n\n- Input: “Refresh breaks when new columns appear.”  \n  Output: Defensive missing-column logic + column normalization + typed schema plan.\n\n","tags":{"latest":"1.0.0"},"stats":{"comments":0,"downloads":5059,"installsAllTime":30,"installsCurrent":30,"stars":4,"versions":1},"createdAt":1768664081350,"updatedAt":1778485733200},"latestVersion":{"version":"1.0.0","createdAt":1768664081350,"changelog":"Initial release of excel-weekly-dashboard.\n\n- Designs refreshable Excel dashboards using Power Query, structured tables, validation, and pivot reporting.\n- Focuses on creating repeatable weekly KPI workbooks that update from files with minimal manual work.\n- Supports ingestion from CSV, XLSX, and exported table formats (DOCX/PDF).\n- Provides clear input requirements, step-by-step workflow, and sample output formats.\n- Defines robust validation, error detection, and refresh status features to ensure reliable weekly reporting.\n- Includes safety guidelines to prevent accidental overwrites and highlight extraction risks.","license":null},"metadata":null,"owner":{"handle":"kowl64","userId":"s173svt85h7w25jh0sc5g59ds1885fgy","displayName":"KOwl64","image":"https://avatars.githubusercontent.com/u/59417033?v=4"},"moderation":null}