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
– Alejandro Lopez-Lago - MSFT, Commented Jun 27, 2017 at 4:58

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

1

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"})))))

answered Aug 28, 2017 at 11:24

Jatin Sanghvi

2,3691 gold badge29 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Power Query - Sum Variable Number of Columns

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related