Forum Discussion
Excel Power Query API Get Call duplicating calls when loading
I am making an API call in Excel Power Query. The data comes into PQ, I parse and transform the data. The issue comes into getting the data out of PQ. If I try to save it as a table in the worksheet, upon loading it calls the API again and if the list is more than 1k rows, it fails. If I to keep the query as a connection only and load to the data model, it calls the API url again and it also fails if there are more than 1k rows.
I have tried to use Table.Buffer, transform the data, copy the data (this fails because the number of cells), reference the API call Query, filtering, and load the query to the data model but they all call the API again. It ends up maxing out my API call Quota. I have searched through so many forums and websites and I have exhausted all I know for resources. I do not understand why the data will not cache after the call and allow me to pull the data into a table without making another call to the API or failing. Any thoughts, course of action or even a cursing would be great at this point. Thank you in advance for any input.
Here is a link to the file setup- I have removed the API Key but you can see the structure
Power Query itself is not the storage for the data, it only transforms it. You may see result of the transformation in Power Query Editor in preview mode. To keep transformed data you need to load result into the grid and/or data model. Each time you load or refresh data Power Query repeats evaluation again.
Table.Buffer is mainly to fix some steps in the memory to avoid partial re-evaluation, but that's to make next steps more reliable.
SergeiBaklan Thank you for clarification. Is there no way to make the call from the source and stop future calls for the same data while you transform and load the data already sourced?
Based on your sample file you have now field which could indicate that's old or new data, like Date. Thus you have to reload everything from scratch.
