•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Dynamic MoM/WoW KPI matrix with mixed data types on rows (Power Query / Pivot Table)
Our take
Managing a dynamic Month-over-Month (MoM) and Week-over-Week (WoW) KPI matrix in Excel can be challenging, especially when dealing with mixed data types. Currently, a Python script extracts KPI data via Trino SQL, which is then manually imported into Excel through Power Query. The goal is to automate this workflow, allowing new date columns to auto-populate upon refreshing the Pivot Table without losing formatting for various data types. Overcoming this hurdle will streamline reporting and enhance productivity in the absence of a dedicated BI tool.
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]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Creating Pivot Table from Multiple SheetsHi All, I'm working on a large tracking workbook, consisting of several clinical trials in order to track by patient detail the payments we are owed by the funder, what we have received, and the difference. All these payments are delayed by 3m-2 years in some cases and leadership wants to accurately predict how much we are owed. I think what where I'm running into issues is that while I did standardized as much as I could, there are still several columns for each study that don't apply to other studies. I.e. some studies have different arms they could be enrolled in, some are just a 1 time enrollment payment, others have several milestones that can receive payments. But every sheet has roll ups that are standardized that I need in the Pivot Table. Those being: Protocol Randomized Date Federal Accrued Foundation Accrued Industry Accrued Supplement Accrued Federal Received Foundation Received Industry Received Supplement Received Total Owed The Accrued and received columns sum the individual payments into those buckets, that way we can go back to the funder and ask specifically what we are missing for to see if they missed paying us for that milestone specifically. When I tried pulling all these sheets into Power Query, I was able too, and aggregated all the sheets into one via Power Query. Then I tried to pull that aggregate into a pivot table. No Pivot table Loaded and all I got was "load to data model failed" on each queries. Am I asking for too much? Can I get rid of the extra columns in Power Query that do no align together with ruining the data that is being pulled in by formulas. I have if statements pulling into the table for the individual, study specific milestones, from a separate table that automatically helps us track payments accrued, and the "standard columns" have sums formulas that sum the columns that apply to them from the individual milestone columns. The milestone, study specific received columns are entered in manually and have no formulas, but are rolled up into the standard columns just like in the accrued side. And the total owed column is also a formula of the standard accrued and received columns. The goal of pulling this into a pivot table is so we can give high level data to leadership to actually start tracking how much we are owed, given the constant delay in payments, and to have a real sense of the deficit this specific program runs year to year. This way they can accurately plan for the yearly "donation" from other sources of funding in the department. If you made it through this post, thank you! Any help is appreciated. I'm using Excel 365. submitted by /u/Melodic-Pollution-91 [link] [comments]
- Pivot Table top bar date filter doesn't update in chronological orderI have a an excel file which I had been building from scratch from a downloaded report every month and am trying to better automate since I do the same type of data filtering each month. I decided to create a table with the data that is linked to a pivot table which sorts and filters it into the format that I need as long as I manually adjust a couple things. However, whenever I add new data to the table, the date filter at the top of the pivot table does not show in chronological order. (Accountant working on April data and need to remove anything that was posted in May.) Theoretically I could use the timeline feature, but I'd rather not. I have resorted the source table in chronological order; I have removed the filter and re-added it in various formats. I've changed the data source, played with various settings. Is there a quick "fix" for this without manually creating the pivot table each time? I have several of these pivot tables on a sheet extracting different data sets sourced from the same table. In one of them, I need to see ONLY the items with a May date, so having to scroll through to check or uncheck the correct dates if they are out of order could lead to errors. Additionally, assuming that this can be corrected. Is there a way for an excel sheet to automatically add or remove row (by hiding or deleting, don't care which) as a pivot table gets longer or shorter so I don't have to guess how many rows I need to add before it cuts into other data below it? Sometimes I need 70+ rows and others only 30. I'm sure there's an overall better way to do what I'm doing. Thanks in advance. https://preview.redd.it/56optogx0qzg1.png?width=406&format=png&auto=webp&s=551180d29effbcfedb7038f60612e33f4e897d3d submitted by /u/inez6453 [link] [comments]
- Excel Dashboard - InteractiveHi. I have to build an Excel dashboard to share with Senior Leadership. I can't build it in Power BI as licenses are limited and I am not allowed access to a workspace to publish for those without licenses. The data set is taken from our IT self service ticket system. I have it formatted as a table with a few helper columns to cleanse issues. I need to present info on tickets by: -directorate -divisions -ticket category -status (open or closed) -length of time open or by length time to resolve when closed. -It has to be visual and not just pivots so needs graphs etc. -It needs to be interactive in a way that the one workbook with the data and dashboard will be available to the Senior Leadership of each directorate or division, who need to be able to filter or use a slicer to amend the visuals for their own areas, and then go to the data set to see only those tickets. (1) i can create pivots tables and charts based on the table, and then slicers based on these, but they don't modify the table which is a requirement. (2) I can create slicers directly from the table which will modify it but then I can't connect these to a pivots table or chart to modify them on the dashboard itself. (3) this will be saved on sharepoint so I can't use any macros if they are a solution, and it has to be dummy proof - with all the goodwill in the world, someone will make a fuss if they can't see something or do something, and it will come back to bite me. Has anyone any ideas on how I can do this? I've tried searching for templates etc but I can't really work out the best way of doing this? I always thought i was pretty decent at Excel but dashboards are not something I would ever really create to be honest. Thanks in advance submitted by /u/liamlarmour [link] [comments]
- Auto-Size Tables Based on Feeder Tables' InputsSo this issue is not as simple as it seems. I do not want solutions for Power Query. This issue is for applying to a tool for a different end-user. We use O365 desktop software. I have two tables, Table1 and Table2, for the purpose of pasting raw CSV data. Some days there are 150 rows, other days there are 200 or 140 rows... Row counts vary. Table1 can also have an unequal number of rows compared to Table2. When adding raw data, a table naturally expands for more rows, but does not do the opposite for fewer rows. Table3 is the combined table, where I use direct references to pull in data via "=Table1[@Col1]", for as many columns as I need. Table2 data is then XLKP'd into Table3 via a primary key column in Table1 and Table3. I am effectively creating a LEFT JOIN using Table1, but need Table3 to auto-size itself. The problem is that Table3 will NOT auto-expand the number of rows when there are more rows in Table1 than Table3 currently has. I have tried named ranges instead of tables, but the user prefers pivot tables and the features of actual tables in Excel, so using ranges and dynamic arrays are not the solution this time. (I love DA's but end users need more flexibility). So I am requesting solutions on how to get Table3 to auto-expand (and even auto-shrink but I do not believe that exists). I have exhausted Google.. it keeps telling me to use PowerQuery and dynamic arrays. The LOL part is that tables HATE having dynamic arrays in them, so i think it's funny that google recommends it. Also, I believe i remember solving this problem in the past, but it eludes me. submitted by /u/gutsyspirit [link] [comments]