Unpivoting help please!

[Anonymous]

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!

[CNENFRNL]

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

[rsbin]

@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,

[Anonymous]

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?

[themistoklis · Accepted solution]

@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"

[Anonymous]

Perfect, what a hero!

Thanks @themistoklis  😊