Forum Discussion
Power BI dataflow very slow
Hi georgec96,
Another option to consider, if you need all that data, is to use direct query for dataflows.
https://docs.microsoft.com/en-us/power-bi/transform-model/dataflows/dataflows-directquery
I've had to use this recently for a large dataset and it works well. It then gives you the option to build aggregation tables in your model to improve performance but also keep the detailed data when necessary.
Caveat, this will only work with premium or premium per user as you need to be able to turn on the 'Enhanced compute engine settings' for the Dataflow.
Hope this helps.
These suggestions are helpful, but I would like to understand better what PBI Desktop is re-downloading the data from my dataflow every time I apply a transformation. If I am performing a simple operation such as adding two columns together to create a new column, why is PBI re-downloading the data?
When you create a calculated column or a measure in Power BI, the data is not re-downloaded because these operations are performed at the Data Model level rather than in Power Query. Since the calculated column is created at the Data Model level, it does not require Power Query to re-download the data because it has not made changes to the source data as such.
In contrast, when adding a new column using Power Query, access to all the data to perform transformations / manipulations is required. Therefore, with each new change in Power Query, once you hit "Apply" or "Close & Apply," Power Query will need to re-download all of the data again.
Importantly, the benefit of making transformations in Power Query is that they do not slow down the Data Model view. If anything, the transformations / manipulations via Power Query often lead to much faster processing in the front end and far greater system efficiencies / speed.
In summary, when you create a calculated column or a measure, the operation is performed at the Data Model level and does not require Power Query to re-download the data. However, it will slow down the speed / efficiencies in the front end (subject to how much data your Data Model has). Conversley, when you use Power Query to do the changes, you have much greater speeds / efficiences, but whenever you make these changes, you then need to "re-download" the data.
Hope this helps.
Theo 🙂
Thank you for your response Theo,
I do undertand that changes to things like measures in Power BI do not require a re-download and that makes sense to me. What I am having trouble understanding is, when I am applying transformations in Power Query, why is the existing data in my preview being re-downloaded? Today, I created a new column in Power Query by combining two columns, which triggered a re-download of the dataflow. When I realized that I didn't like the column name that I chose, I simply changed the name of the column, and it re-downloaded all the data again. This is before I have even clicked "Apply" or "Close and Apply". This makes for a very slow and frustrating design experience no matter how small I shrink my dataset. I don't understand why it is necessary, and it leads me to think that I am either doing something wrong, or perhaps the technology (dataflows) is just new, and they haven't had a chance to optimize it yet.
