Forum Discussion
Complex 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".
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
Hey ImkeF,
Could you please elaborate more on where to write this code?
Is there a way to use the GUI to do that?
Thanks
Pls check this video, which contains the "less-codiest"-way I can think of (for this specific technique):
This is awesome solution.
I've spend few days trying different ways of achieving same result, but this is by far the best - fastest and cleanest.
I wonder if this could be made into custom function?Also, is there a solution to speed up subsequent actions after this join? To be honest, performance of PQ after this kind of join falls of the cliff and even simple filter takes ages (and this is for a dimension kind of table, with just about 1000 rows)
