I'm trying to merge 2 queries. One of them is an (oracle) database view with a column "customerNumber", the other is an Excel sheet that contains the relevant customer numbers for this report. I make sure that the columns have the same data type (in my case text since in some cases there are letters in there), click "Merge Queries" and choose "Right outer Join" with the database Query as the first source and the Excel as the second. The result is something like:
#"Merged Queries" = Table.NestedJoin(#"Source", {"customerNumber"}, excelTable, {"column1"}, "table", JoinKind.RightOuter).
After this step, "View Native Query" is greyed out, which after some googling i found out indicates that there is no folding. I can confirm this by just applying the changes, since i can tell from the "loaded rows" progress window that it does a full table scan. Next i tried to find out more via the diagnostics options. This feature has insonsistent behaviour. When i click the step prior to the Merge, a step where i removed some rows, and click "Diagnose Step", the diagnostics for this step immediately show up as tables in the left pane. When i do the same for the "Merged Queries" step, nothing happens.
I need to click "Start Diagnostics", then do the Merging Step, then "Stop Diagnostics". In the now appearing diagnostics table, there are some SQL statements. Unfortunately, i can't look at them fully, because the viewing window cuts the text off with "..." at the end, with no option to view the whole text. Only when i right click and "Add as new Query", i finally see the SQL statement...
Here i see exactly what i was expecting. There is no trace of a WHERE clause that filters for the desired customer numbers. PowerBI loads the whole table, then internally filters for the customer numbers specified in the excel sheet. I can't find the article any more but after lots of googling i found out that by changing the line to
#"Merged Queries" = Table.Join(#"Source", {"customerNumber"}, excelTable, {"column1"}, JoinKind.RightOuter).
and running Diagnostics again, i finally get an SQL statement that encapsules the customer numbers in "WHERE customernumber = XXX" clauses.
This works as expected in the Data Preview Window. "Refresh Preview" works quick, confirming that there is no full table scan happening.
And now here comes the punch line.
If i now click "Close & Apply", it still executes a full table scan.
I don't know where to go from here. Diagnostics tells me a correct SQL statement, which then doesn't get executed and PowerBi does a full table scan instead.
