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".
Community ChampionA 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".
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
- ImkeF
Pls check this video, which contains the "less-codiest"-way I can think of (for this specific technique):
HermesHelper I
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)
- MarcelBeug
The solution below I applied similarly in an Excel / Power Query application I recently developed ( ImkeF knows: the account matching application), which is used in an interactive way, so it had to be fast.
You can add key columns to Table 2, each with the first n (1-7) characters of the value in Column2, acting as key columns. I did so in below query Table2With7Keys.
Next, you can add a column to Table1 with the length of the value in Column2, and group on that length, so you have partitioned tables for each key length.
Now you can merge those nested tables with Table2, using the key column with the corresponding key length.
Then finish up with some expanding and removal.
Query Table2With7Keys:
let Source = Table2, #"Added Custom" = Table.AddColumn(Source, "Keys", (x) => Table.FromRows({List.Transform({1..7}, each Text.Middle(x[Column2],0,_))}, List.Transform({1..7}, each "Key"&Text.From(_)))), #"Expanded Keys" = Table.ExpandTableColumn(#"Added Custom", "Keys", {"Key1", "Key2", "Key3", "Key4", "Key5", "Key6", "Key7"}) in #"Expanded Keys"Query MergedTables:
let Source = Table1, #"Added Custom" = Table.AddColumn(Source, "Length", each Text.Length([Column2])), #"Grouped Rows" = Table.Group(#"Added Custom", {"Length"}, {{"AllData", each _, type table}}), Merged = Table.Buffer(Table.TransformColumns(#"Grouped Rows",{{"AllData", each Table.NestedJoin(_,{"Column2"},Table2With7Keys,{"Key"&Text.From(List.Average(_[Length]))},"Table2Data",JoinKind.LeftOuter), type table}})), #"Expanded AllData" = Table.ExpandTableColumn(Merged, "AllData", {"Column1", "Column2", "Table2Data"}, {"Column1", "Column2", "Table2Data"}), #"Expanded Table2Data" = Table.ExpandTableColumn(#"Expanded AllData", "Table2Data", {"Column1", "Column2"}, {"Table2.Column1", "Table2.Column2"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Table2Data",{"Length"}) in #"Removed Columns"
Hi ImkeF,
This solution was very helpful. Really grateful. I have a question regarding the type of join. I would like to use a full outer join, in order to see all rows from the First and Second. I tried this, but I still keep getting only all rows from the second table. Is there a way to get around this
=Table.SelectRows(Table.Buffer(Table1), (FullOuter) => Text.StartsWith([Column2], FullOuter[Columns]))
- ImkeF
Yes, the "Inner" is just a random name for the "inner" of the nested functions in that command. So you can name it anything and it wouldn't change the result.
If you want to have a full outer, you can simply perform a full outer after that first operation (and merge the results of the 2 columns together)
Thank you!
what's the difference between inner and outer? are those the join kinds? or is something like "earlier" in DAX?
- ImkeF
Yes, it's like EARLIER in DAX :)
