Forum Discussion

viveksasi94's avatar
viveksasi94
Icon for Helper I rankHelper I
7 years ago
Solved

Distinct count row elements and make row elements as header

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...
Need Help
Show and Tell
  • PattemManohar's avatar
    PattemManohar
    7 years ago

    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"

Gazzer's avatar
Gazzer
Icon for Resolver II rankResolver II
7 years ago

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...