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! 

4 Replies

  • 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

      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

        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.

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion

    Do you actually need this as a physical table in your model? If you need to see it as a visual, all you need is to create a measure using COUNT for the discount field and create a matrix with the customers as rows, the discount field as columns and the measure as values. So...

    Count Discount = 
    COUNT(fTable[Discount])