Forum Discussion
Using SQL statement Source in DirectQuery causes "not supported" errors
Hi Anonymous , I can reproduce this.
To my knowledge, to ensure the performance of DirectQuery, the Power Query query must achieve query folding for a DirectQuery or Dual storage mode table. It means a Power Query query will generate all the steps you make in Query Editor into a single query statement to retrieve and transform source data. This is achieved in your Navigation source table. However, this is not achieved in your SQL statement table because Power Query cannot modify your SQL statement. It treats the specific SQL query statement "as provided", without an attempt to rewrite it.
You can click View Native Query to check whether it is able to achieve query folding. However, the first Source step is already not query folding, but the table is in DirectQuery mode.
Reference:
Query folding guidance in Power BI Desktop - Power BI | Microsoft Docs
DirectQuery model troubleshooting in Power BI Desktop - Power BI | Microsoft Docs
Regards,
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.
Hi Anonymous , I can reproduce this.
To my knowledge, to ensure the performance of DirectQuery, the Power Query query must achieve query folding for a DirectQuery or Dual storage mode table. It means a Power Query query will generate all the steps you make in Query Editor into a single query statement to retrieve and transform source data. This is achieved in your Navigation source table. However, this is not achieved in your SQL statement table because Power Query cannot modify your SQL statement. It treats the specific SQL query statement "as provided", without an attempt to rewrite it.
You can click View Native Query to check whether it is able to achieve query folding. However, the first Source step is already not query folding, but the table is in DirectQuery mode.
Reference:
Query folding guidance in Power BI Desktop - Power BI | Microsoft Docs
DirectQuery model troubleshooting in Power BI Desktop - Power BI | Microsoft Docs
Regards,
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.
Great explanation, thanks!!
In case anyone reads this, the most telling line from that MS Doc:
"It's important to understand that the Power Query mashup engine can't fold later query steps, and so you should include all—or as much—transformation logic in the native query statement."