Forum Discussion
Syndicate_Admin
Administrator
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 q...
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
BA_Pete
Super User
Super UserHi 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