Forum Discussion
Solved
Reorganizing data
Hi, I wanted some help to rearrange my table in Power Query 🙂
Currently, I have one column for each initial and end date for three different trips.
| Person | Start Trip 1 | End Trip 1 | Start Trip 2 | End Trip 2 | Start Trip 3 | End Trip 3 |
| Peter | 01/01/2022 | 07/01/2022 | 28/01/2022 | 04/02/2022 | 16/02/2022 | 23/02/2022 |
| Maria | 01/01/2022 | 07/01/2022 | 28/01/2022 | 04/02/2022 | 16/02/2022 | 23/02/2022 |
| John | 06/01/2022 | 17/01/2022 | 18/02/2022 | 24/02/2022 | 08/03/2022 | 13/03/2022 |
I wanted to unpivot that so the table would like the one below. I have a column with the trip number and the start and end date from each trip.
| Person | Trip | Start Date | End Date |
| Peter | 1 | 01/01/2022 | 07/01/2022 |
| Peter | 2 | 28/01/2022 | 04/02/2022 |
| Peter | 3 | 16/02/2022 | 23/02/2022 |
| Maria | 1 | 01/01/2022 | 07/01/2022 |
| Maria | 2 | 28/01/2022 | 04/02/2022 |
| Maria | 3 | 16/02/2022 | 23/02/2022 |
| John | 1 | 06/01/2022 | 17/01/2022 |
| John | 2 | 18/02/2022 | 24/02/2022 |
| John | 3 | 08/03/2022 | 13/03/2022 |
Yes it's an Unpivot (all date columns).
Split the resulting column (by Space as far right as possible)-> that gets the numbers into a column.
Then Pivot the 'Start Trip'/End Trip' Column with Don't Aggregate
1 Reply
Yes it's an Unpivot (all date columns).
Split the resulting column (by Space as far right as possible)-> that gets the numbers into a column.
Then Pivot the 'Start Trip'/End Trip' Column with Don't Aggregate