Dynamically select columns to sum Get&Transform

JuanSombrero

In this scenario, you can unpivot those columns first, filter columns contains "turnover" or "sales". And sum those values together.

I have a sample table like below:

1. Unpivot all columns except the group column ("Year" column).

2. Then filter matched rows.

4. Sum Values on Year level.

If you want to show the "Filtered Column Total" along with all original columns, you can merge it back to source table.

See my entire Power Query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNFPSUTIEYQMDCAmhgISpUqwOWIk5kGMEwmA5IwMoBSTMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Year = _t, Sales1 = _t, Sales2 = _t, Turnover1 = _t, Turnover2 = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Sales1", Int64.Type}, {"Sales2", Int64.Type}, {"Turnover1", Int64.Type}, {"Turnover2", Int64.Type}, {"Amount", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Year"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each Text.Contains([Attribute], "Turnover") or Text.Contains([Attribute], "Sales")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Year"}, {{"Filtered Column Total", each List.Sum([Value]), type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Year"},#"Grouped Rows",{"Year"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Filtered Column Total"}, {"NewColumn.Filtered Column Total"})
in
    #"Expanded NewColumn"

Regards,