Forum Discussion
Power Query load from Dataflow is excruciatingly slow
I'm a lttle at my wits end with this. Occassionally PowerQuery is very very slow to load previews of data, as in I've seen this before, but recently it's gotten really bad, today is unbearable. My current pain point is trying to load a PowerQuery connected to a Power BI dataflow. The PowerQuery is 2 steps, "Source" and "Navigation". This PowerQuery has no other steps.
I have cancelled the "refresh all" operation and tried to just refresh on of this table that has a "?" next to it. The table is not loaded to the model as it's used in other tables which are. At this point I'm just trying to get a refresh of the table to load so I can go to the other tables and get them to preview. Once I've got them all previewed I can click close and apply where hopefully I can get the Power BI reporting refresh command to run.
I have litterally been at this for hours and made zero progress. I've turned off all my antivirus functions and it hasn't made a difference. I have access to 1 Gbps Internet but my traffic's barely hitting 5 Mbps, most of the time it's running about 4 Mbps.
This is unusable in it's current state. I've run into it before and all the tricks I've used before (like cancelling the refresh all and then refreshing each table individually) are not getting me anywhere. I've run out of work day trying to add one table to my model. The saved PBIX is 29 MB. I have 9GB of available RAM on my computer and the CPU is basically idling. I don't think I'm dealing local resource limitations.
Has anyone else see this? What can I look for as a culprit?
Try clearing the cache in Power BI Desktop.
If that doesn't help, is this on a laptop? Go to Starbucks or someplace else and see if the internet there is better. if that works, there could be a routing issue from your location that is causing a problem, or some other corporate firewall.
Also, you don't have to wait for the preview to finish to load. Just close Power Query and tell it to save (and apply) the data. Even if it fails the first time, hitting Refresh in Power BI desktop usually clears that up.Edit: oh, what dataflow connector are you using? Does the Source line say PowerPlatform.DataFlow or PowerBI.dataflow? Whatever it says, switch it out. I personally prefer the PowerBI.Dataflow as for raw speed it is faster, but doesn't allow folding or setting incremental refresh with it.
12 Replies
Try clearing the cache in Power BI Desktop.
If that doesn't help, is this on a laptop? Go to Starbucks or someplace else and see if the internet there is better. if that works, there could be a routing issue from your location that is causing a problem, or some other corporate firewall.
Also, you don't have to wait for the preview to finish to load. Just close Power Query and tell it to save (and apply) the data. Even if it fails the first time, hitting Refresh in Power BI desktop usually clears that up.Edit: oh, what dataflow connector are you using? Does the Source line say PowerPlatform.DataFlow or PowerBI.dataflow? Whatever it says, switch it out. I personally prefer the PowerBI.Dataflow as for raw speed it is faster, but doesn't allow folding or setting incremental refresh with it.
Thanks for replying. I'll try clearing the cache today. This morning when I retried at home I was prompted for a PowerBI update, working on downloading it now. When I was trying at work I was still seeing very low bandwidth utilizations, but I was able to get the test PowerQuery preview to load, it was still slow, but it did load.
Regarding your other suggestions/questions:
Yes it's laptop and it seems to be reproducible even off the local office networkRE: Preview. I have some tables in Power BI that give me refresh error if I don't go into PowerQuery and get all my previews to be "good", so I need to get the previews in PowerQuery working so the full refresh will actually work.
I am using PowerPlatform.Dataflow connector. I had switched all of the sources in the model to the PowerPlatform becaue I thought that's where Microsoft was heading longterm. I forget where I 'heard' that.
They are, and in fact the Power BI dataflow connector says "legacy" and at some point they will remove the ability to create new connections, but I think we are a LONG LONG way from existing ones breaking. I would set one up with the Power BI connector and see if you can see a speed difference.
Recapping what I did and where it got me:
Yesterday I tried to allow it to refresh during the business day.
I started with trying to do a data refresh on the Power BI side and allowed it to run for ~ 2 hours and it did not complete. This was no substantial changes to the original model when I posted my original question.
I was running out of time in the day at this point.
Because I've seen previously where 'cancelling and trying again' has produced a successful refresh, I retried again and allowed it to run for about 45 minutes without completing.
I cancelled again and this time cleared the data cache as suggested by edhans. Looking back at this I may not have cleared the right cache.
I tried another refresh but could only give it about 20 minutes before I needed to leave. Still didn't have a successful refresh.Last night and this morning I repointed all the dataflows to the PowerBI dataflow connector instead of the Power Platform Dataflows connector and retried.
I was able to get a full Power BI refresh in about 6 minutes.
A side note that during this refresh I did see my network download traffic get significantly higher (100 Mbps+ at some points) and my CPU was "working harder" than it had ever been when I originally posted this question. It seemed like Power BI was actually doing something.
Still going to keep an eye on this to see if something resurfaces, but this experience is not giving me confidence in the Power Platform Dataflow connector.
-- editThe day after I posted this initially, I was in the office and was back to horribly slow refresh times even with the PowerBI dataflow connector.
I spent more time on this and tracked it down to a traffic shapping rule on the firewall which was limiting my bandwith to 512 KBps (~4Mbps) after changing this my refresh time went from ~1hr 20 to ~23 minutes and the "during refresh" experience on my computer was much better.
Hoping this will help someone else.Assuming that you are using the Power Platform Dataflows, have you tried switching to the "Legacy" Power BI dataflows and seen if there was a performance improvement.
I have (at this time) abandoned using the Power Platform dataflows.
