Forum Discussion
Helper IMatch Wildcard via Relationship or Merge
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 |
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 tablenow 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
4 Replies
- Omid_Motamedise
Super User
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 tablenow 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
Community Champion
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 - PwerQueryKees
split, (trim the result), merge and (possibly) group by is your way forward....
- 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
