Name: Excel weekly dashboards at scale
Availability: InStock
Author: kowl64

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.

Excel weekly dashboards at scale

AI Skill Market Insights

Be Part of the 0+ Developer Community

Excel weekly dashboards at scale

PURPOSE

WHEN TO USE

INPUTS

OUTPUTS

WORKFLOW

OUTPUT FORMAT

SAFETY & EDGE CASES

EXAMPLES

Quick Start

Manual Installation

TEAR & SHARE

Tags

Skills of A-share market data released by ft.tech.

AI Data Analysis

Options Strategy Advisor

Analytics Platform Base

Simul8or Trader

Channels

Learn

Compare

Company