Forum Discussion
Unpivot multiple columns
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!!
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
1 Reply
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