Difference between writing a native query and applying a filter in M

[Anonymous]

Hi everyone

I would like to understand if there is any difference, in terms of performance, between writing a native query to filter some rows and doing that same operation in power query steps 

Meaning: let's say I have a Sales Table that I import in my model

Option 1: I write in the native query panel "select * from sales where date > 01012020. Then I load to model

Option 2: I do not write a native query, I simply connect to the table, then apply a filter over the date column saying that the date must be great than 010102020. This creates a query step. Then I load to model 

Is there any difference between this two options in terms of performance? If the topic is more complex and I'm oversimplyfing please refer me to some documentation 

Thanks 

[Greg_Deckler]

@Anonymous My understanding is that if the query can be folded then it should essentially result in being the same thing both ways. However, if it cannot be folded, then Power BI would need to load all of the data and then filter it down which could be a significant performance it. You should be able to test this quite easily as SQL connector allows you to put SQL code into the base query.

[Anonymous · Accepted solution]

AFAIK it's basically as @Greg_Deckler said.

If you're writing custom SQL, you have to be 100% sure of your SQL skills and perform all the transformations in the SQL script, because it will break query folding and you won't be able to fold any step done in the PQ interface.

If your data source supports query folding and you're performing very simple transformations such as filtering columns, these should be folded and you shouldn't notice any difference in performance.