Forum Discussion
Solved
table to bar chart
I'm confused about how to reorganize this data.
I would like to show a bar chart. Each bar is a year, and shows the total GRAC contribution from the projects. I suspect that I need to create a virtual table...
x-axis = year
y-axis = sum of GRAC for that year
Here's what my data looks like.
| ProjectName | GRaC 2023 | GRaC 2024 | GRaC 2025 | GRaC 2026 | GRaC 2027 | GRaC 2028 |
| project apple | 2.15 | 2.49 | 3.03 | 1.22 | ||
| project orange | 0 | 3.45 | 6.97 | 7.53 | ||
| project bananna | 0.75 | 3.2 | 3.82 | 3.82 | ||
| project watermelon | 7.81 | 9.63 | 10.29 | 0.29 | ||
| project tangerine | 0.64 | 6.58 | 6.62 | 4.9 |
First step is to unpivot the data to bring it into a usable form
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY7LCgIxDEV/Zeh6CH0/vmWYRZQiytiWUvD3bUNRB1wkZ5Gc5G4bKzU/4rUtWMoR2cokCEPQoUMBVx0CpOxYqPb1a+WK6TY0Tst6qBaC63Bg1F/ngglTwjnj4Ay5krr/wVl7YYv1GY+cpunAi44AliJykIHuEc5uGynrPcXPU6spqfEEO95pmOL+Bg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjectName = _t, #"GRaC 2023" = _t, #"GRaC 2024" = _t, #"GRaC 2025" = _t, #"GRaC 2026" = _t, #"GRaC 2027" = _t, #"GRaC 2028" = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ProjectName"}, "Attribute", "Value"), #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", Currency.Type}}) in #"Changed Type"Then the visual writes itself.
Yes, you do the unpivoting as part of the ETL in Power Query.
4 Replies
- lbendlin
Super User
First step is to unpivot the data to bring it into a usable form
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY7LCgIxDEV/Zeh6CH0/vmWYRZQiytiWUvD3bUNRB1wkZ5Gc5G4bKzU/4rUtWMoR2cokCEPQoUMBVx0CpOxYqPb1a+WK6TY0Tst6qBaC63Bg1F/ngglTwjnj4Ay5krr/wVl7YYv1GY+cpunAi44AliJykIHuEc5uGynrPcXPU6spqfEEO95pmOL+Bg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjectName = _t, #"GRaC 2023" = _t, #"GRaC 2024" = _t, #"GRaC 2025" = _t, #"GRaC 2026" = _t, #"GRaC 2027" = _t, #"GRaC 2028" = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ProjectName"}, "Attribute", "Value"), #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", Currency.Type}}) in #"Changed Type"Then the visual writes itself.
