Forum Discussion
Find /compare text string segment between two tables
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
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
6 Replies
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
Thanks for responding Owen,
That should never happen.
There should always only be one hit in the "CodeGroup"
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
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 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 🙂
