How to append data point to a table upon every API query refresh?

%3CLINGO-SUB%20id%3D%22lingo-sub-1953841%22%20slang%3D%22en-US%22%3EHow%20to%20append%20data%20point%20to%20a%20table%20upon%20every%20API%20query%20refresh%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1953841%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20a%20Query%20to%20extract%20data%20from%20a%20web%20API.%20What%20I%20end%20up%20with%20is%20a%20table%20of%20my%20data%20that%20refreshes%20every%20minute.%20However%2C%20I%20need%20to%20keep%20a%20time-history%20of%20the%20data.%20Every%20time%20the%20query%20refreshes%2C%20I'd%20like%20it%20to%20append%20the%20new%20data%20to%20a%20table%20that%20keeps%20record%20of%20all%20the%20previous%20query%20responses.%20I%20can't%20figure%20out%20how%20to%20do%20this%2C%20is%20there%20a%20solution%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1953841%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1954057%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20append%20data%20point%20to%20a%20table%20upon%20every%20API%20query%20refresh%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1954057%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F890270%22%20target%3D%22_blank%22%3E%40hasseljr%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDo%20that%20first%20time%20and%20return%20the%20table%20into%20sheet%2C%20let%20say%20query%20name%20(thus%20table%20as%20well)%20is%20GetData.%20After%20that%20modify%20it%20-%20query%20itself%2C%20i.e.%20GetData%20table%20and%20append%20to%20data%20returned%20from%20web.%20That%20shall%20be%20done%20within%20ONE%20query%2C%20now%20as%20couple%20of%20separate%20ones.%20Script%20will%20be%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Web.Content()%2C%20%2F%2F%20if%20other%20steps%20append%20below%20the%20final%20one%0A%0A%20%20%20%20%2F%2F%20query%20resulting%20table%0A%20%20%20%20GetData%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22GetData%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20AppendNextPortion%20%3D%20Table.Combine(%7BGetData%2C%20Source%7D)%0Ain%0A%20%20%20%20AppendNextPortion%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello,

 

I am using a Query to extract data from a web API. What I end up with is a table of my data that refreshes every minute. However, I need to keep a time-history of the data. Every time the query refreshes, I'd like it to append the new data to a table that keeps record of all the previous query responses. I can't figure out how to do this, is there a solution?

1 Reply

@hasseljr 

Do that first time and return the table into sheet, let say query name (thus table as well) is GetData. After that modify it - query itself, i.e. GetData table and append to data returned from web. That shall be done within ONE query, now as couple of separate ones. Script will be like

let
    Source = Web.Content(), // if other steps append below the final one

    // query resulting table
    GetData = Excel.CurrentWorkbook(){[Name="GetData"]}[Content],
    AppendNextPortion = Table.Combine({GetData, Source})
in
    AppendNextPortion