[Dataflow - Power BI Service] When I Reference a Source Its Queries the Source Multiple Times

[Marcio_Santos]

Hello,

I have a Query that makes multiple API Requests, from that Query i create multiple references of that table, why each reference Query makes the API requests again?

Example:
Query 1 (Multiple Requests(Ex. "1", "2", "3"))

Query 2 (References Query 1, some ETL)

Query 3 (References Query 1, some ETL)

Query 4 (References Query 1, some ETL)

What i expect is only 3 API Requests on Query 1 ("1", "2", "3"), whats happening is that on each query its doing the same requests over and over, so in this case its doing the API Requests 12 times instead of only 3 from Query 1, thats why im referencing it to prevent it from making the same requests...

Any solution to avoid this?

Thanks.

[v-heq-msft]

Hi @Marcio_Santos ,
Based on your description, each time Query 1 is referenced in subsequent queries (Query 2, Query 3, and Query 4), the API request is executed again, resulting in redundant data fetches. Ideally, referencing should prevent this by reusing the results of Query 1 without re-executing the API request. There are a few steps you can take to try and see if this meets your requirements
First, make sure the "Enable Loading" option is selected for Query 1. This ensures that the data is loaded into the model and can be referenced by other queries without having to re-fetch it from the source. Second, check subsequent queries (Query 2, Query 3, and Query 4) for any settings that might trigger a data refresh. This includes looking for any steps in the transformation process that may inadvertently cause the data to be reloaded. Finally, if possible, optimize the initial query to minimize the number of API requests or ensure that the data is fetched in a format that requires the least amount of transformation in the referenced query. This may help reduce data complexity and unintended reloads.

Best regards,

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Marcio_Santos]

Hello!!

Thanks for the help, but enabling load will force to use premium capacity.

[lbendlin]

Read about Table.Buffer() - but even then you cannot totally avoid this multiple calls issue. 

[Marcio_Santos]

I tried Table.Buffer() on Query 1 but it didn't help.