Forum Discussion
Excel Power Query Connecting to Datasource locks up all Office products
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.
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.
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
result
Already 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