Forum Discussion

technosmith's avatar
technosmith
Frequent Visitor
2 years ago
Solved

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 o...
Need Help
  • OwenAuger's avatar
    OwenAuger
    2 years ago

    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

technosmith's avatar
technosmith
Frequent Visitor
2 years ago

Thanks for responding Owen,

That should never happen.

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

OwenAuger's avatar
OwenAuger
Super User
2 years ago

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