copy cell values before it refreshes automatic

New Contributor

I using power query to get api from web every 4 minutes, this api only available for send lates data to excell, when data is refresh value change

my question is how to automatic copy cell values in new cells before it refreshes ?





2 Replies
best response confirmed by fakih1993 (New Contributor)

@fakih1993 See attached. The workbook contains a small working example of a technique where you append a PQ table to itself. Suppose the blue table is what you get when you connect to the web api, giving you only the latest value (Source). Load it back to Excel and name the green table called, for instance, "history".  Add a line to the query called Source2, connecting to that "history" table. Add the other applied steps as shown. The very first time you probably duplicate the latest entry. Just delete that row from the "history" table. 


Now, refresh the webapi query (change the date/time stamp in the blue table and refresh, if you want to test it withing Excel first). A new row will be added to the green table, thus building a history of updated values. Every time you refresh, the query will append to Source2 to Source and thus creates a new Source2.



Thankyou I really appreciate