Consider a 2 column dataset as shown below
| User | Location |
| AAA | Tokyo |
| AAA | Osaka |
| AAA | Nagoya |
| AAA | Hakone |
| AAA | Kyoto |
| BBB | Sapporo |
| BBB | Nara |
| CCC | Tokyo |
| CCC | Hakone |
| CCC | Osaka |
| DDD | Osaka |
| DDD | Tokyo |
Customer AAA travelled from Tokyo to Osaka, Osaka to Nagoya, Nagoya to Hakone and Hakone to Kyoto. All locations appear in a single column. To analyse customer travel information very clearly, one may want to rearrange the dataset as follows:
| User | From | To |
| AAA | TOKYO | OSAKA |
| AAA | OSAKA | NAGOYA |
| AAA | NAGOYA | HAKONE |
| AAA | HAKONE | KYOTO |
| BBB | SAPPORO | NARA |
| CCC | TOKYO | HAKONE |
| CCC | HAKONE | OSAKA |
| DDD | OSAKA | TOKYO |
I have solved this problem using Power Query. You may download my solution workbook from here.
Rearrange travel data to clearly show travel from and travel to locations
{ 0 Comments }