Slow "connecting to datasource" step

[zwie]

Hello,

I am using power query on excel and I have been trying to speed up my queries but noticed that the "connecting to datasource" step takes a long time when creating and refreshing a connection. I also noticed that when I am on my work vpn, that step takes around 30 seconds more than the original 5-6 seconds for my largest query.

I don't know too much about connections, but if I am connecting to data on my same workbook with all the queries, why does the vpn affect it? This makes me think the connections are external. If so, is there any way to make an "internal" connection?

I have also tried fiddling with the privacy settings, including the "Always ignore Privacy Level settings" and it seems to have little effect.

I want to get my queries down to below a second so any help or insight on any of this would be greatly appreciated!

[otravers]

Can you show the Source step of your queries?

[zwie]

All of my queries have something similar to

Source = Excel.CurrentWorkbook(){[Name="Table24"]}[Content]

Where the "Table24" is where I put my input table.

Sometimes I pull multiple tables from the workbook so I might have a similar line with SourceB but most of the time I am pulling one table a single time.

[v-jingzhang]

Hi @zwie 

Where is this Excel file located? Is it on your local drive or on somewhere else e.g. OneDrive, Shared folder, etc.?

Best Regards,
Community Support Team _ Jing

[zwie]

Hello @v-jingzhang ,

My file is synced on a OneDrive. I just tried copying the file to a completely local folder on my machine but I get similar problems as before where sometimes a query takes 5-8 seconds and an additional 40 seconds with my vpn.