Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Matrix table

I would like to transform my table as follow: Can anyone her helps me to solve this problem? Thank you in advanced! 
Need Help
amitchandak's avatar
amitchandak
Super User
4 years ago

Anonymous , You need to use pivot

 

https://radacad.com/pivot-and-unpivot-with-power-bi

 

example code

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUfJJLUvNUTBUitVB5huB+Ulo8knI8rEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Discount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Discount", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Discount", "Discount - Copy"),
    #"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[Discount]), "Discount", "Discount - Copy", List.Count)
in
    #"Pivoted Column"
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hei,

 

The table in the right  is showing the count number of Discout (level) each Customer has received.

 

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    4 years ago

    Hi Anonymous ,

     

    1. Duplicate "discount"column

    2. Pivot the "discount" column:

     


    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.