Sum all except x columns in power query

Question

I have a source table which I pivot and then want to sum across all columns except 1 column, here TRADEDATE. The error occurs in the step #"Filled Down"# and a similar error in the next step.

let
    Source = Excel.CurrentWorkbook(){[Name="tbl_equi_funds"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TRADEDATE", type date}, {"ID", Int64.Type}, {"Name Equity", type text}, {"AccNetFlow", type number}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"ID", "Redemption", "Emission", "Netflow"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Name Equity]), "Name Equity", "AccNetFlow"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"TRADEDATE", type date}}),
    #"Filled Down" = Table.FillDown(#"Changed Type1",Record.ToList(
      Record.SelectFields(
      _,
      List.Difference(Table.ColumnNames(#"Changed Type1"), {"TRADEDATE"}), type number))),
 
    #"Inserted Sum" = Table.AddColumn(#"Filled Down", "SUM", each List.Sum(Record.FieldValues(
      Record.SelectFields(_,List.Difference(Table.ColumnNames(#"Filled Down"), {"TRADEDATE"}), type number))))
in
    #"Inserted Sum"

The error I get is

Expression.Error: The name '_' wasn't recognized. Make sure it's spelled correctly.

BlackBear BlackBear · Accepted Answer · 2020-06-26T11:13:03

let
    Source = Excel.CurrentWorkbook(){[Name="tbl_equi_funds"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TRADEDATE", type date}, {"ID", Int64.Type}, {"Name Equity", type text}, {"AccNetFlow", type number}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"ID", "Redemption", "Emission", "Netflow"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Name Equity]), "Name Equity", "AccNetFlow"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"TRADEDATE", type date}}),
    #"Filled Down" = Table.FillDown(#"Changed Type1",
      Table.ColumnNames(#"Changed Type1")),
    #"Inserted Sum" = Table.AddColumn(#"Filled Down", "SUM", each List.Sum(
      Record.ToList(Record.RemoveFields(_, {"TRADEDATE"}))), type number)
in
    #"Inserted Sum"

Sum all except x columns in power query

1 Answers