split multiple column values into multiple rows

[tangutoori]

Dear team,

please look into below sample data adn i want the desired format.

splitign multiples columns and the respective values should shown in respective columns.

Person ID	Type	Order ID	Date of Transaction	Date of Payment
1	Blue	100;101	01/01/18, 02/01/18	02/01/18, 03/01/18
2	Blue	102;103;104	01/01/18, 01/01/18, 02/01/18	02/01/18, 02/01/18, 03/01/18
3	Red	105;106	01/12/2016, 13/04/2015	02/12/2016, 13/04/2015
Output	Foramt
Person ID	Type	Order ID	Date of Transaction	Date of Payment
1	Blue	100	1/1/2018	2/1/2018
1	Blue	101	2/1/2018	3/1/2018
2	Blue	102	1/1/2018	2/1/2018
2	Blue	103	1/1/2018	2/1/2018
2	Blue	104	2/1/2018	3/1/2018
3	Red	105	1/1/2018	2/1/2018
3	Red	106	2/1/2018	3/1/2018

[Zubair_Muhammad]

Hi @tangutoori 

You can add a custom column like following

Then expand it to new rows

=Table.FromColumns({
Text.Split([Order ID],";"),
Text.Split([Date of Transaction],","),
Text.Split([Date of Payment],",")},{"Order ID","Date of Transaction","Date of Payment"}
)

[Zubair_Muhammad]

@tangutoori 

See attached file's query editor for the steps

[tangutoori]

TABLE-1
ID	Author	Author Employee ID	Co Author	Co Employee ID
11718	Jaggarao Karri	22982	7707,45853,33978	CHARAN SWAMY YADAV KOYYA; SUNNAM HIMAVATH PRASAD; VEERA VENKATA SATYANARAYANA ANANTANENI
11135	B V S Krishna Phani	21623
10646	B V S Krishna Phani	21623
10182	Srikanthgandhi Guthi	28516	18050	Gondi Vishak
TABLE-2
ID	Employee	Employee ID
11718	Jaggarao Karri	22982
11718	CHARAN SWAMY YADAV KOYYA	7707
11718	SUNNAM HIMAVATH PRASAD	45853
11718	VEERA VENKATA SATYANARAYANA ANANTANENI	33978
11135	B V S Krishna Phani	21623
10646	B V S Krishna Phani	21623
10182	Srikanthgandhi Guthi	28516
10182	Gondi Vishak	18050