Query folding

Query folding is one of the most powerful and important capabilities of the M language as it translates M expressions into equivalent query statements for the given source system to process. With query folding, Power Query (M) serves as a rich abstraction layer for defining both simple and complex data transformation processes while still leveraging the compute resources of the source system. 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 shown in Figure 2.1, 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:

Figure 2.1: 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...