When building a Query on a SQL Server datasource (and other databases), we can right-click on Steps and see if the View Native Query option is enabled, to be sure that the query up to that step will be sent to the server as a SQL statement (aka "Query Folding"), usually improving performance.
When adding a Merge Queries step, if we specify the Join Kind as the default Left Outer Join, the subsequent steps are still "Native Query" steps. However if we specify Inner Join or others, the subsequent steps are not "Native Query" steps - performance is usually worse.
This is unexpected - it's just as easy (easier?) to code an inner join in SQL and most databases, compared to a left outer join. Some of the other join types also seem easy to code in SQL.
We can workaround by adding expand and Filter steps after a left outer join to effectively force an inner join and keep or Native Query alive, but we shouldnt have to.
Please add Native Query support for Merge Queries steps that use a Join Type: Inner Join.
v-xiaoyan-msft
on:
Power BI Embedded with DirectLake Dataset Error
on:
BUG::SLICER::IDbConnection interface