You need a rule, or rules that you can apply. If you can’t write down the rule, you can’t do it with Power Query.
One rule appears to be, if the word fragment starts with DYN, then remove it. There are complications however. In the Pedi product, -if doesn’t seem to have a space before the product code. Given what I can see, this is what I would do, in order. All of these steps are in the menus
For the first column
1. Replace -if with <space>-if<space>. You would use actual spaces, not <space>
2. Replace two spaces with one space
3. Trim the column (removing leading and trailing spaces
4. Add and index column
5. Split the original column based on delimiter, spaces, into rows
6. I would then add another index row, in case the text gets out of order. I’m not sure if it’s needed.
7. Add a conditional column to identify the codes. Something like “if the text column begins with “DYN”, then true”. You can then use this column to remove all codes beginning with DYN
8. Repeat for all other code patterns
9. Sort on the second index column, again, I’m not sure if it’s needed
10. Remove second index column
11 pivot the text column by selecting just that column and pivoting. There is an option to select. I think it is “do not aggregate” but that’s from memory.
12. delete the index column added in step 4.
This is from memory, but directionally the approach I would use. It may need to be tweaked.
Ken and I have a training course (Power Query Academy) at
Skillwave Training that has 27 hours of coached learning content if you are interested in comprehensive learning.
