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
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 Structure File Sample Read More
