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

OwenAuger's avatar
OwenAuger
Super User
2 years ago

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'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