Splitting Multiple Delimited Columns Into Rows

[pbiuser12345]

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!

[Anonymous · Accepted solution]

try this

[Anonymous · Accepted solution]

try this

[pbiuser12345]

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!

[pbiuser12345]

Worked like a charm - thanks again!

[pbiuser12345]

That is perfect - thanks so much!