Hi all,
I have a tricky one that I'd appreciate some advice on. I'm trying to perform a multi step merge between two tables. I have come up with two solutions which are described below with sample data but the performance of each is terrible when I'm trying to join tables which have 900k rows (I can only get method 1 to work with production data. Method 2 only works with sample data and it just spins forever if I try giving it more than a few hundred rows). I'm joining between a CSV file and a SQL table.
Problem:
Table 1 is a lookup table and table 2 has the data I want to augment. The problem is table 2 may have a valid value for column A, B, C or all three and I want to join to the lookup table in that order of precedence. i.e. lookup table 1 and if you find a match with column A, then use it, else lookup on column B and if you find a match on column B then use it else lookup on column C and if you find a match then use it otherwise null.
Table 1:
| A | B | C | Ref |
| A1 | B1 | C1 | 1000 |
| A2 | B2 | C2 | 2000 |
| A3 | B3 | C3 | 3000 |
Table 2:
| A | B | C |
| a1 | nonsense | nonsense |
| nonsense | B2 | nonsense |
| nonsense | nonsense | c3 |
| nonsense | nonsense | nonsense |
| c3 | nonsense | nonsense |
| nonsense | c2 | nonsense |
| nonsense | nonsense | C1 |
| nonsense | b1 | c3 |
Result:
| A | B | C | ID |
| a1 | nonsense | nonsense | 1000 |
| nonsense | B2 | asdd | 2000 |
| nonsense | nonsense | c3 | 3000 |
| nonsense | nonsense | nonsense | null |
| c3 | nonsense | nonsense | null |
| nonsense | c2 | nonsense | null |
| nonsense | nonsense | C1 | 1000 |
| nonsense | b1 | c3 | 1000 |
Note the last row has valid matches for B and C but B is pulled back according to precedence that I require.
Method 1:
Method 1 just does three table merges and then coalesces the 3 found columns to fulfill my precedence requirement. It works in production but it is very slow.
let
Source = #"Table 2",
#"Uppercased Text" = Table.TransformColumns(Source,{{"A", Text.Upper, type text}, {"B", Text.Upper, type text}, {"C", Text.Upper, type text}}),
#"Merged Queries" = Table.NestedJoin(#"Uppercased Text", {"A"}, #"Table 1", {"A"}, "A Matches", JoinKind.LeftOuter),
#"Expanded A Matches" = Table.ExpandTableColumn(#"Merged Queries", "A Matches", {"Ref"}, {"A Matches.Ref"}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded A Matches", {"B"}, #"Table 1", {"B"}, "B Matches", JoinKind.LeftOuter),
#"Expanded B Matches" = Table.ExpandTableColumn(#"Merged Queries1", "B Matches", {"Ref"}, {"B Matches.Ref"}),
#"Merged Queries2" = Table.NestedJoin(#"Expanded B Matches", {"C"}, #"Table 1", {"C"}, "C Matches", JoinKind.LeftOuter),
#"Expanded C Matches" = Table.ExpandTableColumn(#"Merged Queries2", "C Matches", {"Ref"}, {"C Matches.Ref"}),
#"Added Custom" = Table.AddColumn(#"Expanded C Matches", "Ref", each [A Matches.Ref] ?? [B Matches.Ref] ?? [C Matches.Ref]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"A Matches.Ref", "B Matches.Ref", "C Matches.Ref"})
in
#"Removed Columns"
Method 2:
Method 2 uses a custom function which uses Table.SelectRows to return the value I need. I thought this would be faster but I can't even get it to work on more than a 300 rows. I have tried Table.Buffer() on both table1 and/or table2 and it still won't complete. I have also tried Table.AddKey() which didn't do much either.
let
Source = #"Table 2",
lookup = (a as text, b as text, c as text) as list =>
let
w = Table.SelectRows(#"Table 1", each Text.Lower([A])=Text.Lower(a)),
x = Table.SelectRows(#"Table 1", each Text.Lower([B])=Text.Lower(b)),
y = Table.SelectRows(#"Table 1", each Text.Lower([C])=Text.Lower(c)),
z = if Table.RowCount(w) > 0 then w[Ref] else if Table.RowCount(x) > 0 then x[Ref] else if Table.RowCount(y) > 0 then y[Ref] else {}
in
z,
#"Added Custom" = Table.AddColumn(Source, "ID", each lookup([A], [B], [C])),
#"Expanded ID" = Table.ExpandListColumn(#"Added Custom", "ID")
in
#"Expanded ID"
Does anyone have any thoughts on anything else I can try or something obvious I may have missed. FYI, my real use case has 6 merges but I think the principle should still apply.
