Results 1 to 6 of 6

Thread: Replacing a text string with a text from other tables

  1. #1
    Neophyte prorokrak's Avatar
    Join Date
    Oct 2018
    Posts
    3
    Articles
    0
    Excel Version
    Excel 365 - always current

    Replacing a text string with a text from other tables



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Click image for larger version. 

Name:	Example 2.png 
Views:	20 
Size:	32.3 KB 
ID:	8572

    Thank you very much for your help in advance.
    Attached Files Attached Files

  2. #2
    Conjurer horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    104
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    See attached. A bit messy, but works
    Attached Files Attached Files

  3. #3
    Neophyte prorokrak's Avatar
    Join Date
    Oct 2018
    Posts
    3
    Articles
    0
    Excel Version
    Excel 365 - always current
    Thank you very much. You have obviously put a lot of thinking to the solution.
    It seems a bit intimidating to me since I am very new in Power Query. 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. I guess I will try it during the weekend.

  4. #4
    Conjurer horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    104
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    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/com...elimited-list/ based on trick from Ken Puls ]

  5. #5
    Neophyte prorokrak's Avatar
    Join Date
    Oct 2018
    Posts
    3
    Articles
    0
    Excel Version
    Excel 365 - always current
    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.

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,740
    Articles
    0
    Excel Version
    365
    Just practising on these old threads.
    Attached an offering which uses List.Zip to intercalate strings.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •