Help with Transposing a table to create stacked bar charts

[GW_J]

Hi,

I have a large table with many calculated columns and have been asked to create a report from the data using stacked bar charts.

The relevant part of the data is structured like so (the values are calculated columns):

Key	Equipment	Planning Time	Set-up Time	Operation	Cleanout
1001	Line 1	30	60	500	20
1002	Line 2	15	30	600	60

I beleive for a stacked bar chart I would need the data tranposing like so:

Key	Action	Time
1001	Planning Time	30
1001	Set-up Time	60
1001	Operation	500
1001	Cleanout	20
1002	Planning Time	15
1002	Set-up Time	30
1002	Operation	600
1002	Cleanout	60

I have tried creating a duplicate table in Power Query but this does not duplicate the calvculated columns. I also looked at creating a table in dax (New table = Old table) but that is not shown in power query for me to transpose the data (and my skills in dax are very limited).

Would really appreciate it if someone could point me in the right direction to help solve the issue.

[lbendlin · Accepted solution]

This is called "unpivoting"  and you do it in Power Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFTSUfLJzEtVADGMDYCEGYgwNQCRRgZKsTpgZUYwZSCGoSlCLVRHbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Equipment = _t, #"Planning Time" = _t, #"Set-up Time" = _t, Operation = _t, Cleanout = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Key","Equipment"}, "Action", "Time"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Time", Int64.Type}})
in
    #"Changed Type"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

[GW_J]

Many thanks for the detailled explination! Very much appreciated.