Dynamic MoM/WoW KPI matrix with mixed data types on rows (Power Query / Pivot Table)
I’m trying to automate a dashboarding workflow and have hit a wall with dynamically expanding date columns and mixed formatting. Because we don't currently have access to a dedicated BI tool, all our reporting has to be handled in Excel.
Current Setup:
- I have a Python script that executes Trino SQL queries for these KPIs and dumps the output into
.csvfiles. - I manually import these
.csvfiles into Excel using Power Query and then refresh Pivot Tables built on top of that data.
The Goal: I want to create a matrix showing 10 different KPIs on the rows and time periods (MoM / WoW) on the columns (see screenshot). As new data is written to the .csvs, I want the columns in this view to auto-populate and expand upon simply hitting refresh.
The Blocker: Because the KPIs have mixed data types (Percentages, Currency/₹, and Whole Numbers), I'm struggling to set this up cleanly in a Pivot Table without losing the specific number formatting for each row.
I want to avoid complex, volatile array formulas (OFFSET, INDIRECT, etc.) that require manual updates or break easily. I just want to drop the new CSV, refresh, and have the new date columns appear with the correct formats per KPI.
Currently, I load the csv's into PowerQuery, on which I have made Pivots
(Note: I used AI to help structure and rephrase this problem statement for clarity.)
[link] [comments]
Want to read more?
Check out the full article on the original site