Forum Discussion
AdministratorYTD sum of columns in Power Query based on input from Excel
I hope I have the correct forum.
Within Power Query I am trying dynamically add columns based on an input from Excel.
The input would be something like this from a cell within Excel (I point a query to this cell)
The above could be selected as Jan, Feb, Mar etc to Dec.
The data has this structure:
| Jan | Feb | etc. | Nov | Dec | YTD | ||
Sales | 100 | 100 | 100 | 100 | 200 | ||
| Expenses | 50 | 50 | 50 | 50 | 100 | ||
I would like to sum the columns Jan to Feb (in this example) into the YTD column dynamically based on the earlier input.
Is this possible?
The formula is easy in Excel but I'm struggling within Power Query.
What is the M formula?
Thanks in advance for any help
Hi Syndicate_Admin JeremyHillier ,
The basic structure of the YTD column calculation would be something like this:
if excelQuery[selectedMonth] = "JAN" then List.Sum({[Jan]}) else if excelQuery[selectedMonth] = "FEB" then List.Sum({[Jan], [Feb]}) else if... ... else //your escape output here e.g. null, 0 etc.This may be shortened if your excelQuery resolves in Power Query to just a value, rather than a table, to this:
if excelQuery = "JAN" then List.Sum({[Jan]}) else if excelQuery = "FEB" then List.Sum({[Jan], [Feb]}) else if... ... else //your escape output here e.g. null, 0 etc.Pete
5 Replies
- BA_Pete
Super User
Hi Syndicate_Admin JeremyHillier ,
The basic structure of the YTD column calculation would be something like this:
if excelQuery[selectedMonth] = "JAN" then List.Sum({[Jan]}) else if excelQuery[selectedMonth] = "FEB" then List.Sum({[Jan], [Feb]}) else if... ... else //your escape output here e.g. null, 0 etc.This may be shortened if your excelQuery resolves in Power Query to just a value, rather than a table, to this:
if excelQuery = "JAN" then List.Sum({[Jan]}) else if excelQuery = "FEB" then List.Sum({[Jan], [Feb]}) else if... ... else //your escape output here e.g. null, 0 etc.Pete
Syndicate_AdminBrilliant! Thank you for your help - works perfectly
- Syndicate_Admin
I faced the same struggling here..thks for the question and answer.
question - wonder the excelQuery coding here is referring to the name manager i have to create in excel?(option list created in Excel)?
many thanksss
excelQuery
- Syndicate_Admin
sorry to ask silly question here (i'm first time P.Query user)
i failed with the code....Can anyone advise? many thanksss alot...
- BA_Pete
Your first List.Sum doesn't have anything to work on. It should be List.Sum( { [Jan] } ).
Pete