Query Folding - Supported and Unsupported Data Sources?

[rodneyc8063_1]

As I continue to learn about Power BI, one of the things that was imparted on me early on was to try and always go for as much query folding as possible for a variety of good reasons.

Generally I was told query folding is possible or not possible based on the following (loosely)

Sources that support Query Folding: Databases like MS SQL, Access etc. - Basically sources that have their own query language

Sources that do not support Query Folding: Typically "flat" files such as xlsx, pdf, csv, txt...

I was asked whether the Microsoft Dataverse, or Common Data Services would support Query Folding. I thought yah! Sure why not? Isnt the Dataverse/CDS just another database?

Someone corrected me and said actually no, query folding isnt supported on the CDS/Dataverse.

I tried to settle this debate with Google, but have not been able to find any documentation speaking to whether or not the CDS/Dataverse is supported or not for Query Folding.

Can anyone point me to something that would prove/disprove query folding is possible for CDS/Dataverse?

Thanks in advance

[rafaelfissBR]

Here's a quick rundown on what supports Query Folding and what doesn't:

Query Folding compatible Sources

Some of the data sources that support query folding are given below:

• OData feeds
• SharePoint Lists 
• Web services
• DirectQuery capable sources like Azure Synapse Analytics, Azure Data Lake Storage, and Azure SQL Data Warehouse
• Exchange
• HDFS, Folder.Contents, Folder.Files

Query Folding non-compatible sources

• Flat files (such as CSV or Excel)
• SharePoint Excel files
• Power Query connections
• Some NoSQL databases like MongoDB or Cassandra

Transformations that Support Query Folding:

• Removing or renaming columns
• Merging or appending queries from the same source
• Numeric calculations
• Joins (e.g., inner, outer)
• Pivot and unpivot

Transformations that Do Not Support Query Folding:

• Merging or appending queries from different sources
• Custom columns using functions with no SQL equivalent
• Adding columns with complex logic that don’t have a counterpart in the source

Source: BIConnector

[Icey · Accepted solution]

Hi @rodneyc8063_1 ,

There is no official document mentioned whether CDS/Dataverse supports Query Folding.

But you can determine when a query can be folded: Query folding | Microsoft Docs.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.