Forum Discussion
Unpivoting help please!
Hi all,
I'm pretty new to Power BI so still trying to figure out how to transform data properly. I have my data in the following format:
| Name | Release | Category | Phase 1 Start | Phase 1 End | Phase 2 Start | Phase 2 End | Phase 3 Start | Phase 3 End |
| Task 1 | April-22 | A | 1/3/21 | 30/3/21 | 1/4/21 | 26/4/21 | 1/5/21 | 20/5/21 |
| Task 2 | May-22 | B | 1/6/21 | 24/6/21 | 1/7/21 | 19/7/21 | 1/8/21 | 25/8/21 |
I'm need to transform this data so that I can use a Gantt chart so I know my target is:
| Name | Release | Stage | Start Date | Duration (or end date) |
| Task 1 | April-22 | Phase 1 | 1/3/21 | x |
| Task 1 | April-22 | Phase 2 | 1/4/21 | x |
| Task 1 | April-22 | Phase 3 | 1/5/21 | x |
| Task 2 | May-22 | Phase 1 | 1/6/21 | x |
| Task 2 | May-22 | Phase 2 | 1/7/21 | x |
| Task 2 | May-22 | Phase 3 | 1/8/21 | x |
I can pivot the start dates, but then become unstuck. I've tried creating two queries where I unpivot the start dates in one, and unpivot the end dates in the other, but that doesn't work. Can anyone please suggest the steps I need to take to get to my target state?
Much appreciated!
Anonymous
Select only the start date fields (3 of them) and then right click --> unpivot columns
Now Select only the end date fields (3 of them) and then right click --> unpivot columns
Add a field where it flags rows where the Start and End Date is on the same phase.
Select these rows
Attached is the PowerBI workspace
let
Source = Excel.Workbook(File.Contents("C:\Dropbox\Share\Sample to Delete\UnPivot.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"Release", type date}, {"Category", type text}, {"Phase 1 Start", type date}, {"Phase 1 End", type date}, {"Phase 2 Start", type date}, {"Phase 2 End", type date}, {"Phase 3 Start", type date}, {"Phase 3 End", type date}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name", "Release", "Category", "Phase 1 End", "Phase 2 End", "Phase 3 End"}, "Attribute", "Value"),
#"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"Name", "Release", "Category", "Attribute", "Value"}, "Attribute.1", "Value.1"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Columns1", "Flag rows", each if Text.Start([Attribute],7) = Text.Start([Attribute.1],7) then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Flag rows] = 1))
in
#"Filtered Rows"
5 Replies
Anonymous,
In PQ first, make sure all your date fields are type Date.
Then select all 6 of your Phase fields.
Then Unpivot Selected Columns only.
Hope this works for you.
Regards,
Thanks for the reply rsbin
That gives me the following
Phase 1 Start x Phase 1 End x Phase 2 Start x Phase 2 End x Phase 3 Start x Phase 3 End x Which isn't exactly what I'm looking for. Any tips?
Anonymous
Select only the start date fields (3 of them) and then right click --> unpivot columns
Now Select only the end date fields (3 of them) and then right click --> unpivot columns
Add a field where it flags rows where the Start and End Date is on the same phase.
Select these rows
Attached is the PowerBI workspace
let
Source = Excel.Workbook(File.Contents("C:\Dropbox\Share\Sample to Delete\UnPivot.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"Release", type date}, {"Category", type text}, {"Phase 1 Start", type date}, {"Phase 1 End", type date}, {"Phase 2 Start", type date}, {"Phase 2 End", type date}, {"Phase 3 Start", type date}, {"Phase 3 End", type date}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name", "Release", "Category", "Phase 1 End", "Phase 2 End", "Phase 3 End"}, "Attribute", "Value"),
#"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"Name", "Release", "Category", "Attribute", "Value"}, "Attribute.1", "Value.1"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Columns1", "Flag rows", each if Text.Start([Attribute],7) = Text.Start([Attribute.1],7) then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Flag rows] = 1))
in
#"Filtered Rows"
Simple game of montage
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkkszlYwVNJRciwoyszRNTICMYHYUN9Y3wgkbmwAYxnqm0AYRmYwlqG+KVTIAMKK1YEaCTLHN7ESYqATWKkZVKkJjGWobw5lWMJZ+hZQVaYQVmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Release = _t, Category = _t, #"Phase 1 Start" = _t, #"Phase 1 End" = _t, #"Phase 2 Start" = _t, #"Phase 2 End" = _t, #"Phase 3 Start" = _t, #"Phase 3 End" = _t]), Cols = Table.ToColumns(Source), Montage = let common = List.FirstN(Cols,3), hdr = List.FirstN(Table.ColumnNames(Source),3) & {"Start", "End"}, phases = List.Split(List.Skip(Cols,3),2) in Table.Combine(List.Transform({0..2}, each Table.AddColumn(Table.FromColumns(common & phases{_}, hdr), "Phase", (r)=>_+1))) in Montage
