Forum Discussion
Splitting Multiple Delimited Columns Into Rows
Hello all,
I have data that contains two columns with comma delimited data, that I need to transform from this:
| Project ID | Name | Technician Names | Customer Names |
| 10 | Project Ten | Alice, Bob, Charles | Quincy, Robert |
| 11 | Project Eleven | Don | Stu, Tiffany |
Into this:
| Project ID | Name | Technician Names | Customer Names |
| 10 | Project Ten | Alice | Quincy |
| 10 | Project Ten | Bob | Robert |
| 10 | Project Ten | Charles | |
| 11 | Project Eleven | Don | Stu |
| 11 | Project Eleven | Tiffany |
I know that I can use PowerQuery and split by delimiter into rows, but when I apply that on both columns I don't of course end up with the above. I'm stumped as to where I can take it from there to get where I want to be, but it feels very much like it should be possible.
Thank you for any help you can offer!
5 Replies
That is perfect - thanks so much!
Hi Rocco,
With my apologies on second look it's almost there but not quite. You will notice in the proposed solution (the 'asis' table), Tiffany has jumped from being a customer to a technician. The proposed transform works only as long as the count of technicians on a given row is >= the number of customers on the same row.
Any thoughts on how that could be addressed? I was wondering if it's possible to slip in dummy values as needed and then remove them after. Not sure if that could be done or not though.
Thank you again!
