Extracting by Delimiter and Unpivot Issue

This is a unique situation and something I cant seem to figure out. Basically, I’ve got 3 columns: (1) opportunity ID, (2) competitor amounts, (3) competitor names.

There is one opportunity ID per row, but up to several competitor amounts and competitor names all in the same field. I need to extract each competitor and associated competitor amounts (extract by delimiter for both and then you have like 5 new columns for each extraction) and get them in to rows in the appropriate sequence. See image below. I also attached the excel backup. Can someone help?

Data Scenario.xlsx (18.2 KB)

Hi Nick,

What I did was split the columns Competitor Name and Amount then combined comp 1 with amount 1 etc. unpivot and split to columns again. If its not clear you can follow allong in the Query Editor.
Data Scenario.xlsx (31.2 KB)


1 Like

Thank you Melissa! Well done! Can you send me the PBI file so I can view the steps in query editor?

Thank you again!


All steps are in the XLSX but here’s a PBIX as well
Data Scenario.pbix (12.7 KB)

1 Like

Melissa - Thank you very much for your help. This is the second time I’ve spent hours spinning my wheels on a challenge and the enterprise DNA forum has helped me resolve almost immediately.

Thank you,