Forum Discussion

masplin's avatar
masplin
Impactful Individual
8 months ago
Solved

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 abs...
Need Help
  • v-echaithra's avatar
    v-echaithra
    8 months ago

    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.

v-echaithra's avatar
v-echaithra
Community Support
8 months ago

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.


  • masplin's avatar
    masplin
    Impactful Individual
    8 months ago

    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

    result

     

    Or 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

     

     

    • v-echaithra's avatar
      v-echaithra
      Community Support
      8 months ago

      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.