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.
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