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"

Gazzer's avatar
Gazzer
Resolver 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...

  • 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"