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

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