Forum Discussion
Excel Power Query Connecting to Datasource locks up all Office products
I have an excle based power query that has a bit of a convoluted query to extract stock data for UK stocks from a web page. Each stock runs 2 functions and there are 83 stocks. This used to work absolutely fine and took a few minutes to complete. Recently the whole thing just locks up and all my other office products seize up (other excel in seperate processes, outlook)
Often I just see "connecting to datasource" and it never ends and just stops responding.
I've stripped out any sensitive data so file is here.
The only queries that run automatically are PriceIT and Index Price. al lthe other queries are fine and I just run them periodically.
Is this because the website I am hitting has put up some barriers making the query extremly slow? Even so why do all my other office apps freeze up?
Really appreciate any advice as driving me a bit nuts. I just bought a new much more powerful PC and exact same happening so it isnt machine specific.
Hi masplin ,
You don’t need to create a separate function for the delay. Just modify your existing QuoteUK/Yahoo function by wrapping the Web.Contents call with Function.InvokeAfter. In the earlier example, x was only a placeholder argument, it's not meant to be your function name.
Here’s how your function should look once the delay is applied:
let
Url = "https://shareprices.com/lse/" & Yahoo & "/",Source =
Function.InvokeAfter(
() =>
Web.Page(
Web.Contents(
Url,
[
Headers = [
#"User-Agent" = "Mozilla/5.0 (Windows NT 10.0; Win64; x64)",
Accept = "text/html",
#"Accept-Language" = "en-GB,en;q=0.9"
]
]
)
),
#duration(0,0,0,1)
)
in
Source
Hope this helps,
Thank you.
9 Replies
Hi masplin ,
Try below things to fix the issue.
1. Add Browser Headers (works very often)
Power Query’s default user-agent looks like a bot.
Wrap your Web.Contents like this:
Web.Contents(
url,
[
Headers = [
#"User-Agent" = "Mozilla/5.0 (Windows NT 10.0; Win64; x64)",
Accept = "text/html",
#"Accept-Language" = "en-GB,en;q=0.9"
]
]
)
This makes Power Query look like a real browser → bypassing many throttles.
2. Add a delay between requests
Especially if you call the same base URL many times.
Example:
(x as text) =>
let
_ = Function.InvokeAfter(() => x, #duration(0,0,0,1)),
result = Web.Contents(x)
in
result
This adds a 1-second delay per request → avoids being blocked.
Turn Off Background Refresh
Excel → Queries & Connections → Properties → Disable background refresh
This prevents Excel UI deadlock.
4. Use Power Automate Desktop or Python to fetch the data
Pull data externally → load to Excel
This avoids locking Power Query entirely.
5. Cache results, if the stocks don’t need hourly updates, store the last successful responses locally.
If my response as resolved your issue please mark it as solution and give kudos.
Great I'll give all these a go.
In terms of it locking up all my other office apps I assume this is point on disable background refrsh. I have already done that and still locks everything up. It didnt used to do this only started a few months back. I cant understand why one process not responding is killing everything office related.
Hi Dinesh
Added Broweser header and seems to make no difference in terms of speed or locking up. It even locks new excel processes started after it tries to connecting to datasource.
Adding the delay I am unclear am I adding that to the function itself or to the query that runs the function?
The function looks like this
(Yahoo as text) as table => let Source = Web.Page(Web.Contents("https://shareprices.com/lse/"&Yahoo&"/", [ Headers = [ #"User-Agent" = "Mozilla/5.0 (Windows NT 10.0; Win64; x64)", Accept = "text/html", #"Accept-Language" = "en-GB,en;q=0.9" ] ])),and the query that runs the function looks like this
let Source = Excel.CurrentWorkbook(){[Name="PriceIT"]}[Content], #"Filtered Rows" = Table.SelectRows(Source, each ([Type] = "IT")), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"RIC", "Money", "Investment"}), #"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"Investment", Order.Ascending}}), #"AddQuote" = Table.AddColumn(#"Sorted Rows", "Quotes", each try QuoteUK([Money]) ),where does this bit go?
(x as text) => let _ = Function.InvokeAfter(() => x, #duration(0,0,0,1)), result = Web.Contents(x) in resultAlready set disable background refrsh does NOT stop it locking up
I have no idea how to use Power Automate or Python to fetch data? The table I am pulling looks like this
with this query
Source = Web.Page(Web.Contents("https://shareprices.com/lse/BRFI/", [ Headers = [ #"User-Agent" = "Mozilla/5.0 (Windows NT 10.0; Win64; x64)", Accept = "text/html", #"Accept-Language" = "en-GB,en;q=0.9" ] ])), Data = Source{1}[Data],just cycling the "BRFI" stock code
Hi Dinesh
I am unclear how I put the delay around my funtion? Do I create a new function where x= old function name
(QuoteUK as text) => let _ = Function.InvokeAfter(() => QuoteUK, #duration(0,0,0,1)), result = Web.Contents(QuoteUK) in resultOr am i editiing my QuoteUK function in some way
(Yahoo as text) as table => let Source = Web.Page(Web.Contents("https://shareprices.com/lse/"&Yahoo&"/", [ Headers = [ #"User-Agent" = "Mozilla/5.0 (Windows NT 10.0; Win64; x64)", Accept = "text/html", #"Accept-Language" = "en-GB,en;q=0.9" ] ])),Much appreciated any advice
Seems to work ok-ish on my pi-hole protected network. Since most of the data sources are accessed in anonymous mode you will want to set the data source privacy to "Ignore" - that will greatly improve performance at the expense of security.
There's at least one data source that balks.
Hi. Where do you se t it to ignore? I can go into power query/ data source settings and set each credential to none/public/organisational. I set the web ones to public
The one that is balking is the main data colelctor that is cycling thorugh the stocks on shareprice.com. When i click refresh in power query it doesnt prompt me for any credentials and you dont need a login to access the webpage, so not sure where I should enter these.
Hi masplin ,
Make your Power Query web requests friendlier (throttle + headers + retry). Add a delay before each Web.Contents call and add retry logic. Put this into your stock function (the one that currently calls Web.Page(Web.Contents(...))).
Reduce total number of web calls. If the site exposes a bulk page or API, use it instead of 83 separate page requests. A single multi-stock endpoint or CSV will be dramatically faster and avoid throttling. If no bulk endpoint exists cache responses locally, save raw HTML or results to a local folder and refresh only a subset each run. Only refresh PriceIT and Index Price when needed (manual refresh), keep others cached.
nsert the delay + headers + timeout code into your stock function (copy the function above).
Try running only a small subset of stocks (5) to confirm behaviour.
If still freezing, use the VBA macro to refresh queries sequentially (run macro instead of Refresh All).
If still bad, move fetching to Python/Power Automate Desktop and let Excel read the saved CSV.
Investigate whether the website started rate-limiting (try manual curl/wget from another IP).
Hope this helps.
Thank you.I am unclear how I put the delay around my funtion? Do I create a new function where x= old function name
(QuoteUK as text) => let _ = Function.InvokeAfter(() => QuoteUK, #duration(0,0,0,1)), result = Web.Contents(QuoteUK) in resultOr am i editiing my QuoteUK function in some way
(Yahoo as text) as table => let Source = Web.Page(Web.Contents("https://shareprices.com/lse/"&Yahoo&"/", [ Headers = [ #"User-Agent" = "Mozilla/5.0 (Windows NT 10.0; Win64; x64)", Accept = "text/html", #"Accept-Language" = "en-GB,en;q=0.9" ] ])),Much appreciated any advice
Hi masplin ,
You don’t need to create a separate function for the delay. Just modify your existing QuoteUK/Yahoo function by wrapping the Web.Contents call with Function.InvokeAfter. In the earlier example, x was only a placeholder argument, it's not meant to be your function name.
Here’s how your function should look once the delay is applied:
let
Url = "https://shareprices.com/lse/" & Yahoo & "/",Source =
Function.InvokeAfter(
() =>
Web.Page(
Web.Contents(
Url,
[
Headers = [
#"User-Agent" = "Mozilla/5.0 (Windows NT 10.0; Win64; x64)",
Accept = "text/html",
#"Accept-Language" = "en-GB,en;q=0.9"
]
]
)
),
#duration(0,0,0,1)
)
in
Source
Hope this helps,
Thank you.
