Query folding is one of the most powerful and important capabilities of the M language as it translates M expressions into SQL statements that can be executed by the source system. With query folding, M serves as an abstraction layer to implement both common and complex data cleansing and transformation operations while still leveraging source system resources. When implementing any remaining logic or data transformations via M functions, a top priority of the dataset designer is to ensure that these operations are folded to the data source.

In the following M query, a Table.RemoveColumns() M function is applied against the SQL view for the Internet Sales fact table to exclude three columns that are not needed for the dataset:

The additional step is translated to a SQL query that simply doesn't select the three columns. The specific SQL statement passed to the source system can be accessed by right-clicking the final step in the Query Settings pane and selecting View Native Query. If the View Native Query option is grayed out, this indicates that the specific step or transformation is executed with local resources.

Query folding is limited by the data source of the M expression with relational databases, such as SQL Server and Oracle, supporting the most query folding. Alternatively, no query folding is possible when an Excel workbook or a text file is the data source of a query. The M queries against these file sources will use local M engine resources and thus the volume of data imported as well as the complexity of the query should be limited. Other sources, such as SharePoint lists, Active Directory, and Exchange, support some level of query folding, though significantly less than relational databases.