Hi All,
I'm now struggling to get dynamically sum up columns, based on an input selection from Excel.
From the table below, you can see [Jan] -> [Dec] displayed from Column B to M.
| Column A | Column B | Column C | Column D | Column I | Column J | Column K | Column L | Column M |
| Jan | Feb | Mar | Aug | Sept | Oct | Nov | Dec | |
| Sales | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 |
| Expense | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 |
Base on my month selection in Cell below (let's assume this is Cell N1), i want Query to auto sum figures from [Jan] column B up to [ending month] input in excel cell N1.
| Month |
| Oct |
How could i make it dynamic in P.Query Editor? Thanks alot 🙂
Hi Tan,
Your data is in the wrong layout which is resulting in the wrong approach. You should use Power Query to unpivot the month columns, then you can use a PivotTable to create the summary you want.
If you're still stuck, please provide a sample Excel file with your data and desired result so we can help you further.
Mynda
Hi Tan,
attached is an example of calculation with Power Query.
Does this help you?
Mel
