I am trying to merge two tables in Power BI Power Query Editor. The data mart uses a "partition-view" partitioning method. The first "table" in my merge is a view of 31 tables containing a total of about 27 million rows. So I want to keep the .pbix file in Direct Query mode.
Inspecting the "Native Query" property after performing my two merges, I see that Power Query Editor has created a LEFT OUTER JOIN for each merge. The documentation for Table.NestedJoin() explains this:
By default, a left outer join is performed if a joinKind is not specified.
Changing joinKind to INNER, either through the M code or through the Merge dialog behind the "Edit Settings" option for that step (which, of course, just generates M), causes the file to start using Import mode.
I have been looking for a web page or site that lists which M functions or options force a file into Import mode, but have not yet found that information.
Can the merge be configured to use an INNER JOIN in a way that will cause the file to remain in Direct Query mode? It seems like it should be possible since it's such a minor change to the SQL Power Query is generating.
The general solution to a PQ transformation "breaking the fold" is to use SQL directly in Value.NativeQuery to force the operation to run on the back-end as part of the first query step.
The first "table" in my merge is a view of 31 tables containing a total of about 27 million rows.
That sounds really problematic for DirectQuery. Using DirectQuery on top of complex transformations is, at best, complex.
27 million rows is not that many for Power BI, and you might be able to aggregate the results so you don't import that many, or use Incremental Refresh to speed up refresh.