Forum Discussion

viveksasi94's avatar
viveksasi94
Helper 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"

viveksasi94's avatar
viveksasi94
Helper I
7 years ago

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's avatar
Gazzer
Resolver II
7 years ago

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"