Power Query load from Dataflow is excruciatingly slow

[jnickell]

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? 

[Anonymous]

I'm facing this issue now but the clear cache method doesn't work.

[jnickell]

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. 

[jnickell]

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.
-- edit

The 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.

[edhans]

Glad it worked so far @jnickell - yeah, I am not a fan of the Power Platform Dataflow connector for this reason. I only use it where I need incremental refresh. You cannot do that with the Power BI connector.

[edhans · Accepted solution]

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.

[jnickell]

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 network

RE: 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.

[edhans]

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.

[jnickell]

Once I got to the office this morning, I installed the update Power BI had prompted for. 

I was able to get a full refresh completed of my model this morning. It took several (2 at least) hours. I'm going to try and make some lost time right now.

Right at the moment I don't want to take the additional time/risk of trying to test the PowerBI dataflow connector, but I will keep that in mind when/if this resurfaces. 

Thanks for taking the time to respond to my cry for help.

[edhans]

Great. If you do test the Power BI connector and it works better, and you need help swapping out the M code, let us know. It is a 30 second code swap in the Advanced Editor with no need to redo any other transformations.

In the mean time, could you mark one or more as the solution that at least guided you on this so this thread can be marked as closed? Thanks, and have a good weekend!

[jnickell]

Could you outline what you'd call the 30 second switch between Power Platform dataflows and PowerBI dataflows.

I'm still having issues with this (have been focused on my AV) which I thought was causing the issue. haven't got to the cache clearing and remapping the dataflows yet.

[edhans]

Go into Power query:

1. Create a new dataflow connection using Power BI dataflows to that same table.
2. That will generate 3-4 steps, a SOURCE, and a few Navigation steps.
3. Open the Advanced Editor and copy those first few lines. Do NOT copy the "in" that will be on line 4 or 5
4. Go to your original and paste over those same first few lines. (make a backup of that code obviously)
5. At the end of the last line you copied in, you need to add a comma. It was the last line in the original so had no comma.
6. Line 5 (or whatever the first line of the original dataflow you didn't copy over, just make sure the table reference refers to the name of the step before. It will likely be the name of the table but without the underscore.

So say this is your original Power Platform dataflow:

let
    Source = PowerPlatform.Dataflows(null),
    Workspaces = Source{[Id="Workspaces"]}[Data],
    #"9c50e50c-bbe6-4c3a-b6cd-b2623250536c" = Workspaces{[workspaceId="9c50e50c-bbe6-4c3a-b6cd-b2623250536c"]}[Data],
    #"d4c8ee27-89ac-40b7-958f-c3e8e7c69759" = #"9c50e50c-bbe6-4c3a-b6cd-b2623250536c"{[dataflowId="d4c8ee27-89ac-40b7-958f-c3e8e7c69759"]}[Data],
    Products_ = #"d4c8ee27-89ac-40b7-958f-c3e8e7c69759"{[entity="Products",version=""]}[Data],
    #"Filtered Rows" = Table.SelectRows(Products_, each [ProductKey] > 1800)
in
    #"Filtered Rows"

This is the same dataflow but withthe Power BI dataflow:

let
    Source = PowerBI.Dataflows(null),
    #"9c50e50c-bbe6-4c3a-b6cd-b2623250536c" = Source{[workspaceId="9c50e50c-bbe6-4c3a-b6cd-b2623250536c"]}[Data],
    #"d4c8ee27-89ac-40b7-958f-c3e8e7c69759" = #"9c50e50c-bbe6-4c3a-b6cd-b2623250536c"{[dataflowId="d4c8ee27-89ac-40b7-958f-c3e8e7c69759"]}[Data],
    Products1 = #"d4c8ee27-89ac-40b7-958f-c3e8e7c69759"{[entity="Products"]}[Data]
in
    Products1

So I will copy the Source through Products1 lines into my original and it becomes this:

let
    Source = PowerBI.Dataflows(null),
    #"9c50e50c-bbe6-4c3a-b6cd-b2623250536c" = Source{[workspaceId="9c50e50c-bbe6-4c3a-b6cd-b2623250536c"]}[Data],
    #"d4c8ee27-89ac-40b7-958f-c3e8e7c69759" = #"9c50e50c-bbe6-4c3a-b6cd-b2623250536c"{[dataflowId="d4c8ee27-89ac-40b7-958f-c3e8e7c69759"]}[Data],
    Products1 = #"d4c8ee27-89ac-40b7-958f-c3e8e7c69759"{[entity="Products"]}[Data],
    #"Filtered Rows" = Table.SelectRows(Products1, each [ProductKey] > 1800)
in
    #"Filtered Rows"

That is it. All subsequent steps (Filtered Rows in my example) remain unchanged.

5min tops.

Don't forget to delete the newly created dataflow you used to generate the code, or it will load that into the model too. You just needed it to get the code.

[jnickell]

Thanks, was wondering if you had an approach of easily identifying what to tweak in the existing query, without copy/paste. The copy/paste approach works and isn't bad, just a little tedious.

Just finished remapping each of the dataflows in the model. Still waiting to see if that improves things.