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?
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)
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.
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.
and thank you for sharing!