Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.