Merge based on partial string sequence

[johnlhaase]

Hello

I have two table with text number fields. One is the complete number and the other is an offset of the first meaning it could be text 3 through 13 or 2 through 12. A fuzzy merge does not return the correct information. So I would like either table A to return the partial match in table B or have table B return the partial match in A. Table B's number is a slightly different offset in each row. Again these are numbers stored as texts. Any way to do this match?

Table A

TicketNumber

01470614238450

01467031551686

01470655645513

01470680045763

Table B 7061423845 031551686 70655645513 7068004517 7068004515 92

[lbendlin · Accepted solution]

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.

[ronrsnfld · Accepted solution]

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

[Anonymous · Accepted solution]

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.

[ronrsnfld · Accepted solution]

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

[johnlhaase]

Does a contain function work in this application?

[lbendlin · Accepted solution]

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.

[lbendlin]

Table B

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 [Pattern = _t])
in
    Source

Table A

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]),
    #"Added Custom" = Table.AddColumn(Source, "Match", (k)=> Table.SelectRows(#"Table B",each Text.Contains(k[TicketNumber],[Pattern]))),
    #"Expanded Match" = Table.ExpandTableColumn(#"Added Custom", "Match", {"Pattern"}, {"Pattern"})
in
    #"Expanded Match"

[johnlhaase]

Hello

I understand M code a little and do not see where my soures data gets entered into the script. Could you explain it a little more?