Unpivot multiple columns

[doeked]

I am currently struggling with a Gantt chart that I need to make. I need for that the starting dates (I have) and the duration of an activity. The Gantt will have 5 different phases, of which I only have the starting dates. The duration I have calculated from the difference between phase 1 and 2 starting dates. For that I have created a table that looks like this:

Index	Phase 1: Project kick-off	Phase 2: Technical alignment	Phase 3: ABS	Phase 1 duration	Phase 2 duration
1	03-Mar-19	21-Jul-19	17-Nov-19	140	119
2	01-Mar-20	10-May-20	12-Jul-20	70	63
3	null	24-Sep-17	03-Dec-17	null	70

For me, with unpivot funtionality I have created an table that has all the phases nicely listed. However, I need to have a column added that containt the duration of that phase for that specific index no.

Index	Attribute	Value (starting date)	Duration (how to?)
1	Phase 1: Project kick-off	03-Mar-19
1	Phase 2: Technical alignment	01-Mar-20

If I unpivot all columns other columns (than the index), it will look like this. How do I take the duration to another column?

Index	Attribute	Value
1	Phase 1: Project kick-off	03-Mar-19
1	Phase 2: Technical alignment	01-Mar-20
1	Phase 1 duration	140
1	Phase 2 duration	119

Thanks in advance!!

[Anonymous · Accepted solution]

HI @doeked,

I'd like to suggest you only use 'unpivot column' on the date fields, then you can add a custom column with if statement to check the attribute field value and return different 'phase' duration value and remove the original columns.

Result

Full code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1vVNLNI1tASyjQx1vUpzIGxDc12//DIo28QARALZsTrRSkYgXYZgXUZgcQMguxLKNgKbAGabgwgzY7AeYyAzrzQnB2SJiW5waoGuoTnEcpfUZAgbKg3UFRsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, #"Phase 1: Project kick-off" = _t, #"Phase 2: Technical alignment" = _t, #"Phase 3: ABS" = _t, #"Phase 1 duration" = _t, #"Phase 2 duration" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Phase 1: Project kick-off", type date}, {"Phase 2: Technical alignment", type date}, {"Phase 3: ABS", type date}, {"Phase 1 duration", Int64.Type}, {"Phase 2 duration", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Index", "Phase 1 duration", "Phase 2 duration"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Duration", each if Text.Split([Attribute],":"){0} ="Phase 1" then [Phase 1 duration] else if Text.Split([Attribute],":"){0} ="Phase 2" then  [Phase 2 duration]  else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Phase 1 duration", "Phase 2 duration"})
in
    #"Removed Columns"

Regards,

Xiaoxin Sheng