Summarize values over multiple columns

[JannePK]

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 🙂

[Zubair_Muhammad · Accepted solution]

@JannePK 

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"

[JannePK]

Thanks a lot - this worked exactly as expected!