Forum Discussion
Solved
Summarize values over multiple columns
Hi,
can't get my head around the following problem:
I have a table of company data name, country etc including five "category" columns which may have data in zero to five of them, this category data coming from a finite set of values.
| Company Name | Country | Service1 | Service2 | Service3 | Service4 | Service5 |
| a | USA | sales | consulting | HR | ||
| b | CAN | IPR | sales | |||
| c | MEX | consulting | legal | marketing | sales | IPR |
| d | USA | HR | IPR | consulting | ||
| e | CAN | marketing | sales | legal | IPR | |
| f | USA | sales | ||||
| g | USA | consulting |
I would like to create a summary table out of this data showing the distribution of service category values over countries.
| sales | consulting | HR | IPR | legal | marketing | |
| USA | 2 | 3 | 2 | 1 | 0 | 0 |
| CAN | 2 | 0 | 0 | 2 | 1 | 1 |
| MEX | 1 | 1 | 0 | 1 | 1 | 1 |
What to do - thanks in advance :)
Following M works with your sample data
Please see the attached file's query editor for steps
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUQoNdgSSxYk5qcVAOjk/r7g0pyQzLx3I8QgCEgpgHKsTrZQEZDk7+gFJz4AgJD0KKKqSgSxf1wh0s3JS0xNzgHRuYlF2KlQMZgDIOJDWFLhzwDZDbEExBWFNKtwx2EyE2QYxAqIjDcOzChiOT4erwWItBCvFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Company Name" = _t, Country = _t, Service1 = _t, Service2 = _t, Service3 = _t, Service4 = _t, Service5 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company Name", type text}, {"Country", type text}, {"Service1", type text}, {"Service2", type text}, {"Service3", type text}, {"Service4", type text}, {"Service5", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Company Name", "Country"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Value] <> "" and [Value] <> " ")), #"Duplicated Column" = Table.DuplicateColumn(#"Filtered Rows", "Country", "Country - Copy"), #"Removed Columns1" = Table.RemoveColumns(#"Duplicated Column",{"Company Name"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Value]), "Value", "Country - Copy", List.Count) in #"Pivoted Column"
2 Replies
Following M works with your sample data
Please see the attached file's query editor for steps
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUQoNdgSSxYk5qcVAOjk/r7g0pyQzLx3I8QgCEgpgHKsTrZQEZDk7+gFJz4AgJD0KKKqSgSxf1wh0s3JS0xNzgHRuYlF2KlQMZgDIOJDWFLhzwDZDbEExBWFNKtwx2EyE2QYxAqIjDcOzChiOT4erwWItBCvFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Company Name" = _t, Country = _t, Service1 = _t, Service2 = _t, Service3 = _t, Service4 = _t, Service5 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company Name", type text}, {"Country", type text}, {"Service1", type text}, {"Service2", type text}, {"Service3", type text}, {"Service4", type text}, {"Service5", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Company Name", "Country"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Value] <> "" and [Value] <> " ")), #"Duplicated Column" = Table.DuplicateColumn(#"Filtered Rows", "Country", "Country - Copy"), #"Removed Columns1" = Table.RemoveColumns(#"Duplicated Column",{"Company Name"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Value]), "Value", "Country - Copy", List.Count) in #"Pivoted Column"Thanks a lot - this worked exactly as expected!
