Forum Discussion

Alicia_Anderson's avatar
Alicia_Anderson
Icon for Resolver I rankResolver 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.

     

Alicia_Anderson's avatar
Alicia_Anderson
Icon for Resolver I rankResolver 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
Icon for Community Champion rankCommunity 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))
   )