table to bar chart

[zachtatum]

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

[lbendlin · Accepted 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.

[lbendlin · Accepted 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.

[zachtatum]

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?

[lbendlin · Accepted solution]

Yes, you do the unpivoting as part of the ETL in Power Query. 

[zachtatum]

Got it. Thank you very much for you time helping me.