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)

Cheers,
Melissa

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!

Nick

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,
Nick

Hi @nickjordan32,

I was looking into some unpivot and pivot scenarios, so I revisited this post.

Now I’ve picked up a few tricks along the way and came up with a better, fully dynamic solution to your specific scenario. Didn’t want to keep that from you, so here it is. . Data Scenario.pbix (41.7 KB)

I hope you find it helpful and if so please mark this answer as the Solution - thanks.

Hi @Melissa

Sound interesting so gave it a try. I have come up with attached solution with bit different approach.

Ankit_Data Scenario.pbix (76.0 KB)

Thanks
Ankit Jain

1 Like

@ankit,

Even better I absolutely love it, tumbs up :+1: and thank you for sharing!
Beautiful illustration of what can be achieved with just a little M code :wink:

Where did you learn or pick this up? Because the M functions you used aren’t available through the UI as far as I know.