Query folding

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:

Power Query Editor: View Native Query

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...