Forum Discussion
Unpivoting multiple date columns
Hi,
I have a table like this -
| Title | Request Start Date | Request End Date | Targeted Start Date | Targeted End Date | Actual Start Date | Actual End Date |
| Item 1 | 7/30/2021 | 9/30/2021 | 7/30/2021 | 9/30/2021 | 5/30/2021 | 7/30/2021 |
| Item 2 | 7/30/2021 | 9/30/2021 | 7/30/2021 | 9/30/2021 | 5/30/2021 | 7/30/2021 |
I need to transform it to something like this
| Title | Date Category | Start Date | End Date |
| Item 1 | Request Date | 7/30/2021 | 9/30/2021 |
| Item 1 | Targeted Date | 7/30/2021 | 9/30/2021 |
| Item 1 | Actual Date | 5/30/2021 | 7/30/2021 |
| Item 2 | Request Date | 7/30/2021 | 9/30/2021 |
| Item 2 | Targeted Date | 7/30/2021 | 9/30/2021 |
| Item 2 | Actual Date | 5/30/2021 | 7/30/2021 |
I tried unpivoting all the start date columns and the end date columns from the first table but that gives me a cartesian product. IS there a way to do thi
Select the first column. Unpivot the othe columns.
Split the centre column (by space, once as far left as possible)
Select the new column that has 'start date' and 'end date' in it. Pivot this column. Choose Value(the date column) as Values. Choose Don't Aggregate from the advanced.
That should be it.
2 Replies
Select the first column. Unpivot the othe columns.
Split the centre column (by space, once as far left as possible)
Select the new column that has 'start date' and 'end date' in it. Pivot this column. Choose Value(the date column) as Values. Choose Don't Aggregate from the advanced.
That should be it.
Perfect. Thank you.
