Forum Discussion
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
- amitchandakSuper User
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"- AnonymousNot applicable
Hei,
The table in the right is showing the count number of Discout (level) each Customer has received.
- V-lianl-msftCommunity 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.
- PaulDBrownCommunity 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])