Distinct count row elements and make row elements as header

[viveksasi94]

Hi everyone, I'm new to Power BI, tried with different discussion forums but not working for me. Kindly help me out with this.

INPUT Table

Number	Column2
1	a
2	a
1	b
4	a
2	b
4	b
4	a
1	b

Desired OUTPUT Table

Number	a	b
1	1	2
2	1	1
4	2	1

Thanks in advance

[ChandeepChhabra]

From the Modeling Tab, click on the New Table option and try this formula 

Output Table = ADDCOLUMNS(SUMMARIZE(Table1,Table1[Number]),"A",CALCULATE(COUNTA(Table1[Column2]),Table1[Column2]="a"),"b",CALCULATE(COUNTA(Table1[Column2]),Table1[Column2]="b"))

Snapshot of the output

[PattemManohar · Accepted solution]

@viveksasi94 Do you want to display the data in the visual like that or want to store/transform the data. If it is just for displaying then use "Matrix" visual.

 If you want to transform that data, then you can achieve that in "Power Query Editor" as below. It will dynamically if there is any new additions in Column2 as well.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpUitWJVjKCs0BiSWCWCYosQgxdFlVHMlwMyIoFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Number = _t, ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"ID", type text}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"ID"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Only Selected Columns", List.Distinct(#"Unpivoted Only Selected Columns"[Value]), "Value", "Attribute", List.Count)
in
    #"Pivoted Column"

[viveksasi94]

Thanks, @PattemManohar I just need the matrix visualization. This helped 🙂  

[Gazzer]

Not pretty, but it works:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpUitWJVjKCs0BiSWCWCYosQgxdFqojFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Number = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"Column2", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "a", each if [Column2] = "a" then 1 else 0),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "b", each if [Column2] = "b" then 1 else 0),
    #"Grouped Rows" = Table.Group(#"Added Conditional Column1", {"Number"}, {{"a", each List.Sum([a]), type number}, {"b", each List.Sum([b]), type number}})
in
    #"Grouped Rows"

Now let someone far more clever than me show you how to do this with a nice bit of DAX...

[viveksasi94]

Thank you @Gazzer for your time and effort, but the dataset that I gave is only to illustrate my problem. I need a dynamic DAX command so that I don't need to mention the row values manually. 

[Gazzer]

Something more like this:

let
 Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpUitWJVjKCs0BiSWCWCYosQgxdFqEDxEqG64CwTCHqYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Number = _t, Column2 = _t]),
 #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"Column2", type text}}),
 #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column2", "Column2 - Copy"),
 #"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[#"Column2 - Copy"]), "Column2 - Copy", "Column2", List.Count)
in
 #"Pivoted Column"