Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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,
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.
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |