API server queried too often

[Michael_Anees]

Hi all,

I have some queries extracting data throuh an API. The API providers (all of them) complained that the same query runs more than 20 times. 

In my queries, this data retrieved from the API server is refernced many times for different aggregation functions, filters, etc. It seems like every time it is referenced, Power Query runs the API query again, which does not make sense.

I used Table.Buffer almost everywhere and it still does not solve the issue. I am afraid, I might need to resolve to a basic Python code, which takes away all the utilities we had in Power Query and is such a waste of the development time we put in that project.

Any ideas would be appreciated.

[Daryl-Lynch-Bzy]

Hi @Michael_Anees - may I suggest using a Dataflow for the API.  This will obtain the data once to the Dataflow table.  Now you can reference the first Dataflow in a second Dataflow or in your Dataset to perform the different aggregations, functions and filters.  Essentially, this approach is using the Dataflow to replace the Table.Buffer function.

[Michael_Anees]

Hi Daryl,

That would be a nice idea. However, the dataflow would need to be able to accept parameters from Power BI to run the API call. Assuming that is easily done, If many users refresh their Power BI simultaneously, they may end up with the wrong datasets because they would be reading data based on other people's parameters. There needs to be another solution.