YTD sum of columns in Power Query based on input from Excel

[Syndicate_Admin]

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

[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

[Syndicate_Admin]

Brilliant! Thank you for your help - works perfectly

[BA_Pete · Accepted solution]

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