Help with transforming table (columns with semi-colon)

[ogureisuo]

Hello everyone,

I have a table where a semi-colon separates the data inside the columns.
I used SplitTextByDelimiter and then tried to unpivot the table, but I did not have the correct output.
Any ideas please? Thank you so much

Input:

ID	Item1	Startdate	Enddate
1	AAA ; BBB ; CCC	01/01/2022 ; 01/03/2022; 01/05/2022	01/02/2022 ; 01/04/2022; 01/06/2022
2	AA ; BBB	01/01/2022 ; 01/03/2022	01/02/2022 ; 01/04/2022
3	BB; CC ; DD ; EE	01/06/2022 ; 01/10/2022; 02/01/2022 ; 02/10/2022	01/09/2022 ; 01/11/2022; 01/31/2022 ; 02/11/2022

Ouput:

ID	Item1	Startdate	Enddate
1	AAA	01/01/2022	01/02/2022
1	BBB	01/03/2022	01/04/2022
1	CCC	01/05/2022	01/06/2022
2	AA	01/01/2022	01/02/2022
2	BBB	01/03/2022	01/04/2022
3	BB	01/06/2022	01/09/2022
3	CC	01/10/2022	01/11/2022
3	DD	02/01/2022	01/31/2022
3	EE	02/10/2022	02/11/2022

[speedramps]

Well done @davehus 

I was working on a solution of my own but I liked how you have merged and unmerge the columns , so I have incorporated that. Hope that is ok?

Click here for my alternative solution 

My solution firstly unpivots the columns, to put all the values in one column.

Then splits that column by the ; delimiter.   Rather that repeating the split for each column.

Then unpivots,  merges, pivots, splits the merges and finally renames the columns.

I think @davehus  should get the kudos for coming up with merge / demerge. Briliant ! 

But I think I have improved it a little. 😀

Perhaps there will be other suggestions.

I find it really interesting to see how different  users solve problems.

😀 😀 😀  😀 

[davehus · Accepted solution]

Hi @ogureisuo ,

Please see example attached, basically I've split by delimter and packaged each once back up again, unpivoted on ID and then split data and removed nulls.

Hope this helps.

Did I help you today? Please accept my solution and hit the Kudos button.

[ogureisuo]

Brilliant solution! Thank you @davehus and @speedramps for the help!! 

[speedramps]

• Thanks @ogureisuo 

Glad we could help.

I have accepted the @davehus solution because he got there first. 

Hope that is ok?

I think you can also click the button to accept my solution. Both work.