Query folding and Native query – very simple explanation

When learning Power Query (in both Power BI and Excel) we can find the name “Query folding”.

What is Query Folding?

Query Folding is an approach, when Power Query is connected to database, and first steps of Power Query, that looks like to be part of Power Query, are actually performed in the source database.

The reason is that the database is faster for this operation and that Power Query can get the data “smaller” – because of filtered or grouped.

How to switch the Query Folding on?

You don´t have to switch it on. It works by default, when using supported sources.

Query Folding is NOT working, if you use your own SQL query for connection. In this case only your query is evaluated in SQL source database and everything after is done in Power Query.

How do I know in which step the Query Folding works? 

If you right click on some step in a query, the option “Native query” is available. Then you can see the SQL query “behind” the M language.

This option is available for the beginning of query, until the first step that cannot be converted to SQL.

This is how the native query looks:

The native query ends when we use some step of query, that can´t be converted to query. From this step the “Native query” option is not available.

Which sources support Query Folding?

Databases like MS SQL, Access etc.

Which sources don´t support Query?

Typically “flat” files such as xlsx, pdf, csv, txt…