Dataset designers should check the final step of each query in the dataset to ensure that query folding is occurring. If all required transformations or logic of an M query cannot be folded into a single SQL statement, the dataset designer should attempt to re-design the query to obtain as much query folding as possible. For example, all common or simple transformations can be implemented in the first four steps of the query so that View Native Query will be visible for the fourth step. The remaining logic can be added as the fifth step of the query and this locally executed step or transformation will be applied against the results of the SQL statement generated from the fourth step of the query.
The Value.NativeQuery() M function can be used to pass a SQL statement to the data source. However, any further transformations applied to the results of this function in the M query will exclusively use local resources. Therefore, if implemented, the SQL statement passed to the data source should either include all required logic for the query or return a small result set that can be further processed with local resources.