topic Splitting Multiple Delimited Columns Into Rows in Power Query

Splitting Multiple Delimited Columns Into Rows

pbiuser12345 — Thu, 17 Mar 2022 18:01:57 GMT

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!

Re: Splitting Multiple Delimited Columns Into Rows

Anonymous — Thu, 17 Mar 2022 18:51:20 GMT

try this

Re: Splitting Multiple Delimited Columns Into Rows

pbiuser12345 — Thu, 17 Mar 2022 18:58:36 GMT

That is perfect - thanks so much!

Re: Splitting Multiple Delimited Columns Into Rows

pbiuser12345 — Mon, 21 Mar 2022 20:15:05 GMT

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!

Re: Splitting Multiple Delimited Columns Into Rows

Anonymous — Mon, 21 Mar 2022 20:52:02 GMT

Re: Splitting Multiple Delimited Columns Into Rows

pbiuser12345 — Tue, 22 Mar 2022 13:03:37 GMT

Worked like a charm - thanks again!