Power Query - Sum Variable Number of Columns

Question

I have a large number of columns that where i am trying to add a column to sum all of those columns except for two (a & B). I think i am close with the below code, but there is something wrong w/ it.

The code would add column x by summing the table #"Renamed Columns" except for columns a & b.

SumColumnsExcept = Table.AddColumn(#"Renamed Columns", "x", each List.Sum(Record.ToList(List.RemoveItems(Table.ColumnNames(#"Renamed Columns"),{"a", "b"}))))

thanks in advance!

Could you post an example of the input and output tables? Do you want to sum each of the column values in a row or do you want the sum of all column values in the table to appear in each cell? — Alejandro Lopez-Lago - MSFT

Jatin Sanghvi Jatin Sanghvi · Accepted Answer · 2017-08-28T11:24:46

I was searching for a fix and stumbled on this page. After some trial and error, following query worked. It would help if someone can come up with a simpler query.

SumColumnsExcept = Table.AddColumn(
  #"Renamed Columns",
  "x",
  each List.Sum(
    Record.ToList(
      Record.SelectFields(
      _,
      List.RemoveItems(Table.ColumnNames(#"Renamed Columns"), {"a", "b"})))))

Power Query - Sum Variable Number of Columns

1 Answers