Too many requests in Web.Contents. Can I share a request?

[trevorgermain]

Hi,

I am querying a web api using Web.Contents in a loop to get paged data. The Api has a throttled request limit of 300 requests per 10 minutes.

{
    "results": {
        "timesheets": {
            "123456": {
                "id": "123456",
                "job_code": "0001",
                "user": "9999"
            }
        }
    },
    "more": "true",
    "supplemental_data": {
        "job_codes": {
            "0001": {
                "name": "Job 1"
            }
        },
        "users": {
            "9999": {
                "name": "Trevor"
            }
        }
    }
}

I use List.Generate, combined with Web.Contents to get all of the data for a specific date range, using the "more" value to determine if more pages are available.

To model, The "timesheets" records become my facts, and the supplemental data becomes my dimensions. The main query creates my fact table, then I reference that query and transform to extract my users and jobcodes dimensions.

By referencing the main query, I wind up making the paged Api calls 3x (one for each table). Ideally, I'd like to have the Api called only once, and populate my 3 tables without having to go back to the source. Is there any way to "cache" the Api requests, and reuse them as the source for multiple queries?

[v-ljerr-msft]

Hi @trevorgermain,

By referencing the main query, I wind up making the paged Api calls 3x (one for each table). Ideally, I'd like to have the Api called only once, and populate my 3 tables without having to go back to the source. Is there any way to "cache" the Api requests, and reuse them as the source for multiple queries?

---

As far as I know, there isn't a way to do it currently.

Here is a good article about Referenced Queries And Caching In Power BI And Power Query, which could help you better understand this issue. 

Regards

[trevorgermain · Accepted solution]

I ended up writing a web api as an intermediary that caches responses in a Redis cache. It seems to work really well for this scenario.

[Anonymous]

Hello @trevorgermain,

I am struggling with the same, but my API endpoint throws back a 500 response for some reason, and that kills my refresh. Could you share the code as well? Is this solution working with scheduled refresh in the Service?

Br, 

Szabolcs

[trevorgermain]

As it turns out, this didn't work in the service at all. I don't think the service supports Web Api very well. It works in Desktop, though. I've actually pulled this out of Power BI and moved it to Excel with Power Query / Pivot Tables to get the desired result. Frankly, I didn't want the customer to have to use Power BI desktop. It's a SharePoint hosted solution that would have been really slick with the Power BI web part, but it won't work with the Web Api.

As for your 500 error, try running Fiddler while making the request, you might be able to see more information about what the actual error is. If it seems to be a legitimate error on the Api, you may have to contact your Api provider for support.

[v-ljerr-msft]

Hi @trevorgermain,

Great to hear the problem got resolved! Could you accept your reply above as solution to close this thread(which could also help others who have similar issue easily find the answer)? 

Regards