Forum Discussion
Difference about time refreshing between dataflow and power query editor
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.
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.