Replacing a text string with a text from other tables

[prorokrak]

Hi,

recently I have discovered the powerful tool Power Query. There is a lot of learning to do but now I would need to do something that is out of my league. I would really appreciate if anyone could help me out with this.

I have three tables with identical indexes.

1. Source text that includes a text with specific tags - in my example file there are "<XXX>" and "<YYY>" tags.
2. Two tables with text that should be inserted to the "Source text", replacing the tags.

Problem is that each line of source text can contain different number of specific tags. Every occurrence of the tag should be replaced with the text from another column. That means that first <XXX> tag should be replaced with value from the first column in the XXX table, second tag should be replaced with the value from the second column and so on. The same applies for <YYY> tag and corresponding table.

The data are changing, so the maximum number of tags in the source text and respective number of columns in XXX-YYY tables can be higher than it is in the moment but I guess that there should never be more than ten tags/columns.

I have prepared an example file illustrating my data, including merging queries. Hopefully the file will be more understandable than my explanation here. See also "Wanted Result Example" sheet.

Here is also graphical illustration of what I want to achieve.


Thank you very much for your help in advance.

 

[horseyride]

See attached. A bit messy, but works

 

[prorokrak]

Thank you very much. You have obviously put a lot of thinking to the solution. :hail:
It seems a bit intimidating to me since I am very new in Power Query. :eek2: Hopefully I will be able to figure out how to apply solution from this simplified example to my real-life data. If I got stuck I will call for help. :israel:I guess I will try it during the weekend.

 

[horseyride]

Happy to help. The overall steps are

1. Use multiple splits on < and > to get XXX and YYY into their own rows, and the before/after/inbetween text into their own rows
2. Add an index to enable resorting later
3. Add a counter (1)
4. Use function to turn counter into a cumulative counter so we know first XXX for Index1/Index2/Index3 combo is #1, second is #2 etc
5. Merge in the XXX and YYY replacements based on 4 keys - Index1, Index2, Index3 and CumulativeCounter
7. Put the original text and replacement text together in one column
8. Resort rows based on #2 above since steps 3-7 jumble the data order
9. Combine all the rows for Column from #7 where Index1, Index2 and Index3 are identical [see https://www.excel-university.com/combine-rows-into-a-delimited-list/ based on trick from Ken Puls ]

 

[prorokrak]

I wanted to confirm that I was able to apply your solution on my data. I have over 30 000 rows in the source text table, and several thousands rows in XXX and YYY tables so it makes my PC sweat when transforming the data, but it does the job just fine.

Thank you again for your help.:rockon:

 

[p45cal]

Just practising on these old threads.
Attached an offering which uses List.Zip to intercalate strings.