Forum Discussion

Alicia_Anderson's avatar
Alicia_Anderson
Resolver I
6 years ago
Solved

How to merge two tables based on a partial match

I have two tables each with one column:  Release Table [Release] & Release CID Table [Release CID]. I want to associate Release CID to Release based on whether there is a exact match first and if no...
Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

    Hi Alicia_Anderson ,

    my code included the small letter L "l" and not 1. 

    The reason for your error message is that you used a number instead. 

    So either replacing it with any character or a string like Jimmy801  mentioned should solve the problem.

     

    BTW: It might be more suitable for your use case to use the "Text.StartWith"- function instead of the "Text.Contains"-function.

     

ImkeF's avatar
ImkeF
Community Champion
6 years ago

Having problems to understand that logic: How about those other duplicates then?

 

 

Alicia_Anderson's avatar
Alicia_Anderson
Resolver I
6 years ago

ABCD 11.1.5 does not exist in CID Release table.

ABCD 11.1 does not exist in CID Release table.

ABCD 11 DOES exist in CID Release table. (MATCH)

 

ABCD 11.2 does not exist in CID Release table.

ABCD 11 DOES exist in CID Release table. (MATCH)

 

ABCD 11.4.0 does not exist in CID Release table.

ABCD 11.4 does not exist in CID Release table.

ABCD 11 DOES exist in CID Release table. (MATCH)

  • ImkeF's avatar
    ImkeF
    Community Champion
    6 years ago

    this is not clear to me. See the 2 issues below:

    • Alicia_Anderson's avatar
      Alicia_Anderson
      Resolver I
      6 years ago

      My apologies.   I altered the data  examples at the last minute and did not reference the correct CID. 

       

      "ABCD 11.6" should have matched to "ABCD 11"

       

      "XYZ 17.1 CU2" should have matched to "XYZ 17.1"

      • ImkeF's avatar
        ImkeF
        Community Champion
        6 years ago

        Hi Alicia_Anderson 

        Thanks, appreciated.

        Please add a column with the following formula:

         

         

        List.First( 
    List.Select( 
        List.Buffer( 
           Table.Buffer( 
               Table.Sort(#"Release CID Table",
                          {{"Length", Order.Descending}}) )
                 [Release CID Table] ), 
        (l) => Text.Contains( [Release Table], l))
   )