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"

ChandeepChhabra's avatar
ChandeepChhabra
Impactful Individual
7 years ago

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