Forum Discussion

GlitterL's avatar
GlitterL
Frequent Visitor
4 years ago
Solved

Multiple populated Matrix - for power bi desktop or report builder

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)

 

DateCategoryLinkColourData

May 2022

carcar_redred5
April 2020busbus_redred15
May 2022carcar_blueblue2
June 2021taxitaxi_redred1
June 2021carcar_redred7

 

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

{(Header) = Category} - Car

 RedBlue
May 202252
June 20217 

 

{(Header) = Category} - Bus

 RedBlue
April 202015 

 

{(Header) = Category} - Taxi

 RedBlue
June 20211 

 

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

Need Help
  • jbwtp's avatar
    jbwtp
    4 years ago

    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

1 Reply

  • jbwtp's avatar
    jbwtp
    Icon for Memorable Member rankMemorable Member
    4 years ago

    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