Match Wildcard via Relationship or Merge

[ABR002]

So I've found some topics on this subject but none seem to pertain to my exact situation. I have 2 tables with codes in ABC-####-##### format. On one there is only one value per cell, but on the other table there are between 1 and 40 values per cell. The only thing I can think of is to do a split by delimiter and then merge each column back into one, making multiple rows of the other columns but that seems like it will be pretty messy. Is there a better way? 

I tried a merge using fuzzy matching and that actually worked pretty well, until I came across some codes that were different by only one digit (see below). I altered the similarity threshold, but it literally goes from All to nothing. Heres an example of the 2 tables, green should link to green and blue should link to blue, but the red code is linking to the blue code which I don't want:

Table 1

Row Number	Table 1 Code
1	ABC-2002-23456
2	ABC-2002-45454
3	ABC-2005-55555
4	ABC-2006-22222

Table 2

Row Number	Table 2 Code
1	ABC-2002-33333, ABC-2002-44444, ABC-2005-23456, ABC-2001-98765
2	ABC-2004-77777, ABC-2005-44444, ABC-2002-23456
3	ABC-2004-88888, ABC-2005-55555, ABC-2002-23456

So what I would want is to have row 1 on table 1 somehow match (via merge or relationship) to rows 2 and 3 on table 2, but NOT row 1, and also row 3 on table 1 to row 3 on table 2. The issue I ran into with fuzzy matching was that it was linking with row 1 because there's a code (red) that only has a 1 digit difference, and there's when I change the threshold it goes from matching what I want and don't want to not matching anything at all. Here is my desired outcome, which again if there's a way to do a relationship on the front end that's fine too:

Table 1 Row Number	Table 2 Row Number	Table 1 Code	Table 2 Code
1	2	ABC-2002-23456	ABC-2004-77777, ABC-2005-44444, ABC-2002-23456
1	3	ABC-2002-23456	ABC-2004-88888, ABC-2005-55555, ABC-2002-23456
3	3	ABC-2005-55555	ABC-2004-88888, ABC-2005-55555, ABC-2002-23456

[AlienSx]

let
    load = (table_name) => Excel.CurrentWorkbook(){[Name = table_name]}[Content],
    tbl_1 = List.Buffer(Table.ToRows(load("Table1"))),
    tx = Table.ToList(
        load("Table2"), 
        (x) => ((w) => if w = -1 then {} else List.Transform(w, (z) => tbl_1{z} & x))
        (List.PositionOf(tbl_1, x{1}, Occurrence.All, (c, v) => Text.Contains(v, c{1})))
    ),
    result = Table.FromRows(List.Combine(tx), {"row 1", "code 1", "row 2", "code 2"})
in
    result

[PwerQueryKees]

split, (trim the result), merge and (possibly) group by is your way forward....

[Omid_Motamedise · Accepted solution]

Consider this table as your second table in power query.

select column code and go to transform tab and pick split column by delimiter and use "," as delimiter to reach the next table

now right click on the column row number table 2 and pick unpivot other columns to reach the next table

you do not need column attribute, by reming this, you reached a tablel apporopriate for merging by the first om

[dufoq3]

Hi @ABR002, it is not a mistake to split by delimiter and merge (which creates duplicated rows), but you can delete them afterwards. You can compare query speed with this (without merge):

Output

let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tci5DQAgDATBXi7GEhw2OVCG5f7b4ElgNxt3FCT0MYU5U1jVGiI5+LPa/nJ9bGKny/q4CU+IWA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Row Number" = _t, #"Table 1 Code" = _t]),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZYxBDoAgDMC+YnbeEhgb4FF8BuH/3zDTZM7YWw/tnJAB4RgncUpMxcDNXQx3JS6i1T3T3ltVWDiB341QM0L23fCzubMSs26ETI1/ti4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Row Number" = _t, #"Table 2 Code" = _t]),
    T2_Ad_Helper = Table.AddColumn(Table2, "Helper", each List.Transform(Text.Split([Table 2 Code], ","), Text.Trim), type list),
    T2_Buffered = Table.Buffer(T2_Ad_Helper),
    T1_Ad_T2 = Table.AddColumn(Table1, "T2", each Table.SelectRows(T2_Buffered, (x)=> List.Contains(x[Helper], [Table 1 Code])), type table),
    ExpandedT2 = Table.ExpandTableColumn(T1_Ad_T2, "T2", {"Row Number", "Table 2 Code"}, {"Table2 Row Number", "Table 2 Code"}),
    FilteredRows = Table.SelectRows(ExpandedT2, each ([Table2 Row Number] <> null))
in
    FilteredRows