Trying to add two columns in Power Query M without requiring a specific name

New to Power Query.

I’m looking for a way to add two columns together by row using Power Query M without requiring a specific name for the column.
The headers are subject to change but always mean the same. (Ex: Dunkin, Dunkin Donuts, DD)

Here is an example of what i’m looking to do, with the “Total” column being the result of the function.

Floor Backroom Total
2 4 6
10 3 13

Previously we were attempting something similar to PA1 + PA2, but it thought we were trying to add the words “Floor” and “Backroom” together instead of the rows below it.

Right now it’s working this way:

#”addCol Fixtr Totals” = Table.AddColumn(Source, “Fixtr Total”, each [#”‘Floor'”] + [#”‘Backroom'”]),

but we need to replace the [#”‘Floor'”] with something that will change when the columns are named different.

You can use Expression.Evaluate() and column indexes.

enter image description here

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Floor", Int64.Type}, {"Backroom", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom",  let
a = Table.ColumnNames(#"Changed Type"){0},
b = Table.ColumnNames(#"Changed Type"){1}
in Expression.Evaluate("each [" & a &"] + [" & b & "]" ))
in
    #"Added Custom"

Add column … custom column … with formula

=Record.FieldValues(_){0}+Record.FieldValues(_){1}

if you have a bunch of columns adjacent to each other you can do

= List.Sum(List.Range(Record.FieldValues(_),xxx,yyy))

and set xxx and yyy based on https://learn.microsoft.com/en-us/powerquery-m/list-range

Leave a Comment