copy cell values before it refreshes automatic

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.