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.
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 |
Solved! Go to Solution.
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.
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.
Thank you.
It worked.
Is there a way that I can unpivot to a new table like you propose, but also still keep the new unpivoted table synced to the source table?
Yes, you do the unpivoting as part of the ETL in Power Query.
Got it. Thank you very much for you time helping me.
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.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |