Dynamically select columns to sum Get&Transform

[JuanSombrero]

Hi all,

I have a number of columns (date, item, sales rep) and multiple turnover columns. I want to dynamically create a list of all columns that contain the words "turnover" or "sales" and want to sum only these columns in an additional column. Because the turnover columns tend to change, I need to dynamically capture the columns containing these words, and pass this list as the argument into List.Sum.

Any ideas?

thx,

Juan

[bwilkey]

Or for the column list you could simply use:

#"ColumnList" = List.Select(Table.ColumnNames(Source), each Text.Contains( _, "Turnover", Comparer.OrdinalIgnoreCase))

Or

#"ColumnList" = List.Select(Table.ColumnNames(Source), each Text.StartsWith( _, "Turnover", Comparer.OrdinalIgnoreCase))

Or Comparer.Ordinal if case matters, and then use ColumnList as the List you pass to other functions.

[v-sihou-msft · Accepted solution]

@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,