Forum Discussion

viera00's avatar
viera00
Helper II
10 years ago
Solved

Complex PowerQuery Merge query using substrings

Hello everyone,   I have a questions about a complex merge I need to do in Power Query. Imagine the following tables:   Table1:   Column1 | Column2 1              4441 2              4442 3...
Need Help
  • ImkeF's avatar
    ImkeF
    10 years ago

    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".

Simonolsson's avatar
Simonolsson
Regular Visitor
3 years ago

Hello, hope this is ok to make this question in this topic, i was the closest case i could find on this page. 

I can't manage to get this code to work, my case i just a little bit different than the origanal post. 

I have this: 

Sheet 1 = Price master

(column B) = P/N

Example: 16510-96J10

Sheet 2 = Exceptions

(Column A) = Part start

Example: 16510-
(Column E) = AG end

Example: 3

 

I want to do like a Vlookup from Price master, the result should be "search for first characters" from Exceptions. 
In excel i can do it like this, but want is integrated in my Query "=VLOOKUP(LEFT([@[P/N]];5);Exceptions!A:D;4;0)"

 

The result should be something like this: 

Search: 16510-96J10 (number found in "Price master"

The match should be for something like this, match from 2 characters to whole number. whichever comes first.

16

165

1651

16510

Numbers found in "Exceptions"

 

Outcome: New column in "Price master" with result from AG end ( "3" in this case)