Forum Discussion
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 4443
4 443
Then I have Table2 , as the following table:
Table2:
Column1 | Column2
1 4441098
2 4441097
3 4441011
4 4441122
5 4443111
6 4443123
And the merge should have the rows of Table1 and the rows of Table2 where the "prefix" of Table2.Column2 is Table1.Column2. Take into account that I do not know the number of chars of the prefix, I mean, Table1.Column2 can have any lenght not greater than 7
Regards GV
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".
15 Replies
well there needs to be a rule about the length of the merged characters. It is not important how long the second table are, but it is important how long the characters in the first table are. Your example suggests that there can be 3 or 4 character lenght in the first table. Assuming that is a typo, I would do this.
Load table 2
duplicate column 2
split column 2 at 4 characters
delete the remainder column
join table 1 on table 2 with the new column.
If your 3 character 443 in table is not a typo, you are in a world of trouble - unless you can identify a rule on how to split the column in table 2.
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".
Hi ImkeF
Interesting Solution.
I will try it and let you know if worked. Thank you very much for your contribution.
Regards,
GV
Hi Matt,
Thank you for your reply, but I'm in a world of trouble, as my 3 chars is not a typo. Even worst, I've records of 5 and 2 chars.
Regards,
GV
Lets say your unique ID has 6 different possible lengths (2, 3, 4, 5, 6, or 7 characters long)
A way you could possibly do this would be to duplicate your query 6 times, split off a different number of characters in each query, and merge only matching rows to the original table. Then at the end you'd need to append all the tables.
It might be messy and a bit slow, but it should work.
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 endExample: 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)
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.
