So I tried implementing the manual incremental refresh logic. With some help from ChatGPT I came up with this code,
=let
// Step 1: Retrieve the previously stored data
storedData = Table.FromRecords({}),
// Function to fetch data from the API
FetchDataFromAPI = () =>
let
// Define the URL of the API endpoint
apiUrl = "https://worldtimeapi.org/api/timezone/Asia/Kolkata",
// Make the HTTP request to fetch data from the API
apiResponse = Web.Contents(apiUrl),
// Convert the JSON response to a table
apiDa = Json.Document(apiResponse),
apiData =
if Value.Is(apiDa, type list) then
apiDa // Return the list of records as is
else
{apiDa} // Wrap the single record in a list
in
apiData,
// Step 2: Fetch data from the API
apiDa = FetchDataFromAPI(),
apiData=Table.FromRecords(apiDa),
finalData=
if Table.RowCount(storedData)=0 then
apiData
else
let
// Step 3: Identify updates
updatedData = Table.Join(storedData, {"unixtime"}, apiData, {"unixtime"}, JoinKind.LeftOuter),
updatedDataFiltered = Table.SelectRows(updatedData, each [unixtime] <> null),
// Step 4: Merge updates with the stored data
mergedData = Table.Combine({Table.RemoveColumns(storedData, {"unixtime"}), updatedDataFiltered}),
// Step 5: Append new rows
newRows = Table.SelectRows(apiData, each not List.Contains(Table.Column(mergedData, "unixtime"), [unixtime])),
finalMergedData = Table.Combine({mergedData, newRows})
in
finalMergedData
in
finalData
The API is a publicly available API that gives the current time and date. An example is
{
"abbreviation": "IST",
"client_ip": "122.176.65.95",
"datetime": "2024-04-01T15:03:59.624664+05:30",
"day_of_week": 1,
"day_of_year": 92,
"dst": false,
"dst_from": null,
"dst_offset": 0,
"dst_until": null,
"raw_offset": 19800,
"timezone": "Asia/Kolkata",
"unixtime": 1711964039,
"utc_datetime": "2024-04-01T09:33:59.624664+00:00",
"utc_offset": "+05:30",
"week_number": 14
}
My logic behind this code is that whenever I click on the "Refresh Preview" button in the Power Query editor, a new response would come, which would check with the "unixtime" field. Now this field would change with every call, so, every new record will be unique. So, according to my code logic, after every refresh, a new row would be added to the previous table. But that did not happen, instead the same row kept on updating. I don't understand where I am going wrong here. In another post Inserting rows recursively does not yield correct results , I found the point of #"Burndown Query", but didn't really understand it. Any help regarding the code would be much appreciated.
Thanks!
