Operation on multiple rows with dynamic columns

theekin · Sep 13, 2017

Hi guys,

Can anyone give me a hand?

Suppose I have a table in Excel with the following information:

Product A B С
1 1,000 2,000 3,000
2 1,000 2,000 3,000
3 1,000 2,000 3,000

and I need to perform an operation, let it be sum, on the multiple rows (from 1 to ...) which can be dynamic. And the sum function needs to include the newly added columns (which are also dynamic, for example, from A to ...).

What would the code in Power Query be?

Thank you in advance,

Alex

Rudi · Sep 13, 2017

Try this...

Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"A", type number}, {"B", type number}, {"C", type number}}),
    #"Sum Except" = Table.AddColumn(#"Changed Type","Total", each List.Sum(Record.ToList(Record.SelectFields(_,List.RemoveItems(Table.ColumnNames(#"Changed Type"), {"Product"})))))
in
    #"Sum Except"

theekin · Sep 13, 2017

Thank you, Rudi! That is a very nice way of summing rows.
I managed to do this thru the merge function, but your way is much better.
Have a nice day!

Operation on multiple rows with dynamic columns

theekin

New member

Rudi

New member

theekin

New member