Find /compare text string segment between two tables

[technosmith]

I am needing to create a new column in table 1 based on the contents of a column in table 2. if any of the text in the rows of table 2 are present in the string field in table 1 , then the contents of the new column field must equal the string segment found in table 2.

The red text in the "code" field in table 1 would be the desired output

Any help appreciated

[technosmith]

Thanks @OwenAuger 

The PBIX visually would achieve what I need. 

Your solution may work, however, I think I may have oversimplified the graphic supporting my question...

In your PBIX attachment I only noted one table, while I will have two tables. I did note in when looking at the Power query editor I did see the two tables. I am not yet familiar enough with Power query editor to understand your proposed solution. Thanks again for taking the time to respond!

[technosmith]

After playing around with the avdanced editor using @OwenAuger solution I applied it successfully to my data. Thanks @OwenAuger !

[OwenAuger]

@technosmith glad it helped!

Yes, both tables are present in Power Query but I had only loaded one. You can certainly load both to the data model if needed 🙂

[OwenAuger]

Hi @technosmith 

What do you want in the new column in the case of multiple matches? Concatenate them in some way? Or can we assume that will never happen?

Regards

[technosmith]

Thanks for responding Owen,

That should never happen.

There should always only be one hit in the "CodeGroup"

[OwenAuger · Accepted solution]

Thanks @technosmith  🙂

I have attached a small PBIX with one approach.

Here is an extract of code for Table 1 with the Code column added:

let
    // ... Earlier steps here
    // Next step finds first matching Code from Table 2
    #"Added Code" = Table.AddColumn(#"Changed Type", "Code", each let CurrentCodeGroup = [CodeGroup] in List.First(List.Select(#"Table 2"[Code],each Text.Contains(CurrentCodeGroup,_))), type text)
in
    #"Added Code"

The Added Code step finds the first Code from Table 2 that is contained in the CodeGroup string.

A null value should be returned in the case of no match.

Regards