Merge based on partial string sequence

yes, the proposed solution is based on Text.Contains.

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

Using List.FindText function to locate the correct full number for a partial number

TableA

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TcrBCcAwDAPAXfLuQ64l2buE7L9GTaHQ73F7LwQLDt7ZFNa5XnIhQwq3P5olmYP5owao8tB5AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TicketNumber = _t])
in
    Source

TableB

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VcuxDQAgCETRXagtQDjEWQj7r6HRWFi+f7lMGuxiXcNA1ZJYBRAPP9oj4LaLPgezQcbP+52dqhY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TicketNumber = _t]),
    
//Add the full ticket number if a match
    #"Added Custom" = Table.AddColumn(Source, "TicketNumberA", each List.FindText(TableA[TicketNumber],[TicketNumber]){0}?, type text),

//add the non-matching TableA numbers
    #"Add from TableA" = Table.Combine({#"Added Custom", 
        Table.SelectRows(Table.RenameColumns(TableA,{"TicketNumber", "TicketNumberA"}), 
        each not List.Contains(#"Added Custom"[TicketNumberA],[TicketNumberA]))
        })
in
    #"Add from TableA"

Result

Hi,

Thanks for the solutions ronrsnfld  and  lbendlin  provided, and i  want to offer some more information for user to refer to,

hello johnlhaase , you can refer to the following solution.

Table A

Table B

Then in table a create a custom column

let a=[TicketNumber]
in Text.Combine(List.Select(#"Table B"[Column1],each Text.Contains(a,_)),",")

Output

And you can refer to the attachments.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.