Filter by SQL statement or Add filter step in Power Query

[TungNguyen_19]

Hi everyone,

Today I found this question: "If I have a large Microsoft SQL Server table (more than 100 million records). During the development process, I need to import a sample of the data from the table. From Power Query Editor, you import the table and then add a filter step to the query. Does this meet the goal?"

The thing I confuse does the data will fully loaded when I click on Transform data windows which mean the solution above is wrong?

Anyone can help me? I need some article or explains. Many thanks to you

[lbendlin · Accepted solution]

The question topic is query folding - the substitution of Power Query transforms with custom queries in the native language of the data source.  Most often that is SQL  (and its variations like SOQL).  

So when you add a filter step to the Power Query actions that filter is then translated into SQL code, and the custom code is run at the data source instead of in Power Query.  You can (mostly) observe that ths is happening by checking the "View Native Query" context menu for each Power Query step.

If you observe that the data is fully loaded then Query folding is not happening because either your source doesn't support it, or you added a transform that could not be translated.