Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JoeFields
Frequent Visitor

How to execute two Web.Contents calls in series in Power Query?

I have a custom connector that does an API call to get a pre-signed S3 URL then it does another call to fetch a CSV from the pre-signed URL. These S3 URLs have a short lifespan of only 60 seconds so it is critical that these two Web.Contents operations execute in series. The custom connector can also be used to fetch many CSVs. For small batches of CSVs there does not seem to be an issue but when a user selects 100+ CSVs to load, we get errors and it appears the signed URLs are expired on some of the calls by the time they are fetched. Is there a way to force two Web.Contents calls to always run in series?

 

Here's a sample of the code where the two calls are made:

 

fetchSignedUrl = (accountId, jobId, filename, header) => 
    let 
        waitForResult = Value.WaitFor(
            (iteration) =>
                let 
                    result =  Web.Contents(
                        base_path & prefix & accountId & "/jobs/" & jobId & "/data/" & filename & ".csv", [
                            Headers = header,
                            ManualStatusHandling = {502},
                            IsRetry = iteration > 0
                        ]),
                    status = Value.Metadata(result)[Response.Status],
                    resultParse = Json.Document(result),
                    actualResult = if status = 502 then null else resultParse[signedUrl]
                in
                    actualResult,
            (iteration) => #duration(0, 0, 0, Number.Power(2, iteration)),
        5)
    in
        waitForResult;

fetchCsvFile = (accountId, jobId, filename, header) =>
    let
        waitForResult = Value.WaitFor(
            (iteration) =>
                let
                    signedUrl = fetchSignedUrl(accountId, jobId, filename, header),
                    result = Web.Contents(signedUrl, [ 
                        ManualCredentials = true,
                        ManualStatusHandling = {403},
                        IsRetry = iteration > 0
                    ]),
                    status = Value.Metadata(result)[Response.Status],
                    _status = Diagnostics.LogValue("StatuCode is",status),
                    actualResult = if 
                            _status = 403
                        then null else Csv.Document(result, [ Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.Csv])
                in
                    actualResult,
            (iteration) => #duration(0, 0, 0, 1),
        5)
    in
        if waitForResult = null then
            error "Value.WaitFor() Failed after multiple retry attempts"
        else
            waitForResult;

 

 

EDIT: I was inspecting the traffic with Fiddler and I am seeing duplicate calls to get the pre-signed URL and download the same CSV. This might be more of an issue with duplicate calls occuring which is slowing things down and causing the pre-signed URLs to expire. I am wondering if the local cache isn't being populated fast enough and other ETL steps are causing duplicate calls. How can I force a wait for the cache to populate without imposing a long delay?

 

Thanks!

1 REPLY 1
v-luwang-msft
Community Support
Community Support

Hi @JoeFields ,

Pls refer the following article,and check if it is helpful.

https://community.powerbi.com/t5/Power-Query/Odd-Web-Contents-behavior-making-multiple-calls-to-API-... 

 

Best Regards

Lucien

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.