Forum Discussion
Helper IIComplex PowerQuery Merge query using substrings
A different way to perform a lookup is to use "Table.SelectRows", although this might be slower - so it is essential that you use the Table.Buffer and for very large table try to partition also. But the basic principle can look as follows:
let Table1 = Table.Buffer(#table({"Column1", "Column2"}, {{1, "4441"}, {2, "4442"}, {3, "4443"}, {4, "443"}})), Table2 = #table({"Column1", "Column2"}, {{1, "4441098"}, {2, "4441097"}, {3, "4441011"}, {4, "4441122"}, {5, "4443111"}, {6, "4443123"}}), Lookup = Table.AddColumn(Table2, "Lookup", (outer) => Table.SelectRows(Table1, each Text.StartsWith(outer[Column2], [Column2]))), #"Expanded Lookup" = Table.ExpandTableColumn(Lookup, "Lookup", {"Column2"}, {"Lookup.Column2"}) in #"Expanded Lookup"Advantage here is that you don't need an exact match, but can use all sorts of conditions, in this case "Text.StartsWith".
I had a similar case and found a solution that worked for me. I had two tables: My goal was to join them, matching any model from Table B that begins with the coorsponsing prefix in Table A.
Table A: With an abbreviated model.
| Model | Region |
| A | US |
| B | EU |
| Test | UK |
Table B: With the full model.
| Model | Quantity |
| A-1 | 1 |
| A-2 | 2 |
| Test-1 | 5 |
Power Query Solution:
Step 1: In table A, add a custom column that brings in Table B for each row. Expand all rows on the new column.
#"TableB"Step 2: Add a column to count the length of the model from table A.
Text.Length([Model])Step 3: Add a column to display the first X characters of table B. X being the column from step 2.
Text.Start([Model], Text.Length([SKU]))Step 4 (Final) : Add a filter rows step, filtering the table to Step 3 = Model from table A.
Table.SelectRows(#"Inserted First Characters", each ([First Characters] = [Model]))I think SQL has a built in syntax so you may want to investigate if you have that option.