Multiple populated Matrix - for power bi desktop or report builder

[GlitterL]

Hey hey, 

I am new to PB, and I'm a bit stuck,

I have this data with different categories, see example below (as I cannot provide the real data due to privacy)

Date	Category	Link	Colour	Data
May 2022	car	car_red	red	5
April 2020	bus	bus_red	red	15
May 2022	car	car_blue	blue	2
June 2021	taxi	taxi_red	red	1
June 2021	car	car_red	red	7

I want each Category to populate its own matrix like below, with the header being the category:

{(Header) = Category} - Car

	Red	Blue
May 2022	5	2
June 2021	7

{(Header) = Category} - Bus

	Red	Blue
April 2020	15

{(Header) = Category} - Taxi

	Red	Blue
June 2021	1

Please assist me and indicate if this is possible, I need to create paginated reports and each category needs to be on a single page

[jbwtp · Accepted solution]

Hi @GlitterL,

Something like this(uing Pivot Column on the Transform menu tab)?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k2s1DUyUtJRSk4sgpDxRakpQBaENFWK1YlWciwo0jUyAHKTSoshJIoiQ4gqLEYl5ZSmgjRAKCOwMq/SPF0jQyC3JLEiE0qhGoeqDLvDzGGKUhWMDIgzDq4Sp4mxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Category = _t, Link = _t, Colour = _t, Data = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Category", type text}, {"Link", type text}, {"Colour", type text}, {"Data", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Colour]), "Colour", "Data", List.Sum),
    #"Filtered Rows" = Table.SelectRows(#"Pivoted Column", each ([Category] = "bus"))
in
    #"Filtered Rows"

Kind regards,

John