Splitting multiple columns using delimiter

[scoobymoo]

Hi

I have a table which includes 3 columns with delimiters. I need to split the columns into rows using the delimiters but maintaining the order of the information in the columns

for example:

I would like this

Person ID	Type	Order ID	Date of Transaction	Date of Payment
001	Blue	100,101	01/01/18, 02/01/18	02/01/18, 03/01/18
002	Blue	102,103,104	01/01/18, 01/01/18, 02/01/18	02/01/18, 02/01/18, 03/01/18
003	Red	105,106	01/12/2016, 13/04/2015	02/12/2016, 13/04/2015

To look like this

Person ID	Type	Order ID	Date of Transaction	Date of Payment
001	Blue	100	01/01/2018	02/01/2018
001	Blue	101	02/01/2018	03/01/2018
002	Blue	102	01/01/2018	02/01/2018
002	Blue	103	01/01/2018	02/01/2018
002	Blue	104	02/01/2018	03/01/2018
003	Red	105	01/01/2018	02/01/2018
003	Red	106	02/01/2018	03/01/2018

I can split one column into rows using the delimiters but is it possible to split them all without losing the relationship?

Thank you

[v-yuta-msft · Accepted solution]

Hi scoobymoo,

Based on my test, it's difficult to insert values from one column to another column in power bi. I woud recommend you to try R script or python script to do this.

Regards,

Jimmy Tao