Forum Discussion
Combine Date Columns and create a phase column
Hi,
At the moment, I have data that looks like this :
| Project Title | Initiation Start Date | Initiation End Date | Design Start Date | Design End Date |
| Project 1 | 2020-01-01 | 2020-05-01 | 2020-07-01 | 2020-12-01 |
| Project 2 | 2022-01-01 | 2022-03-01 | 2022-08-01 | 2023-01-01 |
| Project 3 | 2021-02-01 | 2021-06-01 | 2021-10-01 | 2022-03-01 |
I want to have only one start date and end date column and create a new column which identifies the phase. Anyone knows how to get this transformation done?
| Project Title | Phase | Start Date | End Date |
| Project 1 | Initiation | 2020-01-01 | 2020-05-01 |
| Project 1 | Design | 2020-07-01 | 2020-12-01 |
| Project 2 | Initiation | 2022-01-01 | 2022-03-01 |
| Project 2 | Design | 2022-08-01 | 2023-01-01 |
| Project 3 | Initiation | 2021-02-01 | 2021-06-01 |
| Project 3 | Design | 2021-10-01 | 2022-03-01 |
Anonymous ,
You can do. Please follow the below steps,
Step 1 : Duplicate the source table and remove the "Initiation Start Date", "Initiation End Date" columns from the duplicated table. Your duplicated table should look like the below after removal of the above mentioned columns.
Step 2: Remove the "Design Start Date", "Design End Date" from the source table and then append the duplicate table to the source table. It should look like the below,
and then follow the attached Power query code from Unpivoted Other Columns step , you will get the result.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlEwVNJRMjIwMtA1MAQiOMcUmWOOxDE0AnFidRAmGEGkjJBNAHKMkTkWcI4xVBmyCcYQKaC4EVwdkGOGxDE0wDA7NhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Title" = _t, #"Initiation Start Date" = _t, #"Initiation End Date" = _t, #"Design Start Date" = _t, #"Design End Date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Title", type text}, {"Initiation Start Date", type date}, {"Initiation End Date", type date}, {"Design Start Date", type date}, {"Design End Date", type date}}), #"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"Design Start Date", "Design End Date"}), #"Appended Query" = Table.Combine({#"Removed Columns1", #"Duplicate of Sample Table to append"}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Appended Query", {"Project Title"}, "Attribute", "Value"), #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Start Date", each if [Attribute] = "Initiation Start Date" then [Value] else if [Attribute] = "Design Start Date" then [Value] else null), #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "End Date", each if [Attribute] = "Initiation End Date" then [Value] else if [Attribute] = "Design End Date" then [Value] else null), #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"Value"}), #"Filled Up" = Table.FillUp(#"Removed Columns",{"End Date"}), #"Removed Alternate Rows" = Table.AlternateRows(#"Filled Up",1,1,1), #"Renamed Columns" = Table.RenameColumns(#"Removed Alternate Rows",{{"Attribute", "Phase"}}), #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","Start Date","",Replacer.ReplaceText,{"Phase"}), #"Sorted Rows" = Table.Sort(#"Replaced Value",{{"Project Title", Order.Ascending}}) in #"Sorted Rows"Thanks,
1 Reply
Anonymous ,
You can do. Please follow the below steps,
Step 1 : Duplicate the source table and remove the "Initiation Start Date", "Initiation End Date" columns from the duplicated table. Your duplicated table should look like the below after removal of the above mentioned columns.
Step 2: Remove the "Design Start Date", "Design End Date" from the source table and then append the duplicate table to the source table. It should look like the below,
and then follow the attached Power query code from Unpivoted Other Columns step , you will get the result.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlEwVNJRMjIwMtA1MAQiOMcUmWOOxDE0AnFidRAmGEGkjJBNAHKMkTkWcI4xVBmyCcYQKaC4EVwdkGOGxDE0wDA7NhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Title" = _t, #"Initiation Start Date" = _t, #"Initiation End Date" = _t, #"Design Start Date" = _t, #"Design End Date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Title", type text}, {"Initiation Start Date", type date}, {"Initiation End Date", type date}, {"Design Start Date", type date}, {"Design End Date", type date}}), #"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"Design Start Date", "Design End Date"}), #"Appended Query" = Table.Combine({#"Removed Columns1", #"Duplicate of Sample Table to append"}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Appended Query", {"Project Title"}, "Attribute", "Value"), #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Start Date", each if [Attribute] = "Initiation Start Date" then [Value] else if [Attribute] = "Design Start Date" then [Value] else null), #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "End Date", each if [Attribute] = "Initiation End Date" then [Value] else if [Attribute] = "Design End Date" then [Value] else null), #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"Value"}), #"Filled Up" = Table.FillUp(#"Removed Columns",{"End Date"}), #"Removed Alternate Rows" = Table.AlternateRows(#"Filled Up",1,1,1), #"Renamed Columns" = Table.RenameColumns(#"Removed Alternate Rows",{{"Attribute", "Phase"}}), #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","Start Date","",Replacer.ReplaceText,{"Phase"}), #"Sorted Rows" = Table.Sort(#"Replaced Value",{{"Project Title", Order.Ascending}}) in #"Sorted Rows"Thanks,