Difference about time refreshing between dataflow and power query editor

[margo1395]

I got a very important questions, there we go:

I would like to know which of the two options would take longer to execute, that is, which of the two is less efficient because it uses more resources:

Option 1: Create a data flow with two tables, through an ODBC origin, that allows to bring the origin through a query (for example: select * from table 1 where column 1 in () and column 2 = '')

Option 2: Enter the two tables without any type of restriction (select * from table) and transform them in the power query

Here comes my question ... I have already tried the first option, and I am having many problems due to the high volume of data, the fact is that I do not know if the second option what it will do to me will be: bring me the whole table, transform it, but the cached data of the entire table will take up space and will be less efficient, would that be the case? Or perform power query makes power bi only execute what has been "clean" of the tables in the update? Thanks in advance,

Maria.  

[ibarrau · Accepted solution]

Hi. Even though Power Query is amazing for transformations, the fastest way will always be a good data base engine. If you are running big native queries from Power Bi to the engine and it's taking too long or won't work, I would suggest creating a store procedure to handle all that. Create a procedure that results in a single table with all transformations/joins/cleanings. Then Power Bi can just take "select * from clean_table". That will be the fastest way to handle it. I don't think that moving transformations from a good query to Power Query will be faster unless you are making some performance mistakes in the query (because Power Query will try to query fold it with some transformations).

If you still want to try Option 2. I strongly suggest reading about Query Folding in order to use the transformations that can be traslated to SQL engine from Power Bi.

I hope that make sense.