Query Folding... Why it is important?
I am not sure, how many of you know this or understand this, but this is very important in PowerBI
Query folding is the ability for a Power Query query to generate a single query statement to retrieve and transform source data. The Power Query mashup engine strives to achieve query folding whenever possible for reasons of efficiency.
Query folding is an important topic for data modeling for several reasons:
Import model tables: Data refresh will take place efficiently for Import model tables (Power Pivot or Power BI Desktop), in terms of resource utilization and refresh duration.
DirectQuery and Dual storage mode tables: Each DirectQuery and Dual storage mode table (Power BI only) must be based on a Power Query query that can be folded.
Incremental refresh: Incremental data refresh (Power BI only) will be efficient, in terms of resource utilization and refresh duration. In fact, the Power BI Incremental Refresh configuration window will notify you of a warning should it determine that query folding for the table cannot be achieved. If it cannot be achieved, the objective of incremental refresh is defeated. The mashup engine would then be required to retrieve all source rows, and then apply filters to determine incremental changes.
Query folding may occur for an entire Power Query query, or for a subset of its steps. When query folding cannot be achieved—either partially or fully—the Power Query mashup engine must compensate by processing data transformations itself. This process can involve retrieving source query results, which for large datasets is very resource intensive and slow.
Transformations that can achieve folding
Relational data source transformations that can be query folded are those that can be written as a single SELECT statement. A SELECT statement can be constructed with appropriate WHERE, GROUP BY, and JOIN clauses. It can also contain column expressions (calculations) that use common built-in functions supported by SQL databases.
Generally, the following list describes transformations that can be query folded.
Removing columns.
Renaming columns (SELECT column aliases).
Filtering rows, with static values or Power Query parameters (WHERE clause predicates).
Grouping and summarizing (GROUP BY clause).
Expanding record columns (source foreign key columns) to achieve a join of two source tables (JOIN clause).
Non-fuzzy merging of fold-able queries based on the same source (JOIN clause).
Appending fold-able queries based on the same source (UNION ALL operator).
Adding custom columns with simple logic (SELECT column expressions). Simple logic implies uncomplicated operations, possibly including the use of M functions that have equivalent functions in the SQL data source, like mathematic or text manipulation functions. For example, the following expressions returns the year component of the OrderDate column value (to return a numeric value)
Date.Year([OrderDate])Pivoting and unpivoting (PIVOT and UNPIVOT operators)
Transformations that prevent folding
Generally, the following list describes transformations that prevent query folding. This is not intended to be an exhaustive list.
Merging queries based on different sources.
Appending (union-ing) queries based on different sources.
Adding custom columns with complex logic. Complex logic implies the use of M functions that have no equivalent functions in the data source. For example, the following expressions formats the OrderDate column value (to return a text value).
Date.ToText([OrderDate], "yyyy")Adding index columns.
Changing a column data type.
Determine when a query can be folded
In the Power Query Editor window, it is possible to determine when a Power Query query can be folded. In the Query Settings pane, when you right-click the last applied step, if the View Native Query option is enabled (not greyed out), then the entire query can be folded.
Another good read article published by MS on privacy settings is here: https://docs.microsoft.com/en-us/power-bi/admin/desktop-privacy-levels