Merging incremental data from Web source

[SaurarbhUpland]

I am trying to pull the data from a Web source, the API I am referring to provides a feature to pull the data based on the last updated date, however, I am not able to merge the data correctly on the PBI side, my use case is to pull everything from the Web API first and then pull the records based on the latest updated at field, I tried the native incremental refresh by PBI, but instead of updating the existing records(based on the Id), new records are created, I want to update the existing records instead of updating it, is there a way to achieve this?

For example, when we run the first query, we get the following records

ID	name	updated_at
1	Foo	2024-07-29 14:54:37
2	Bar	2024-07-29 15:54:37

Now in the next API call, I want to make the call to the same API, with parameter updated_at_gt_eq="2024-07-29%2014%3A54%3A37" and let's say it provides the following data

[{

  id: 1,

  name: "Foo (Updated)"

  updated_at: "2024-07-30 14:54:37"

}]

So now I want to update the PBI Table to the following

ID	name	updated_at
1	Foo (Updated)	2024-07-30 14:54:37
2	Bar	2024-07-29 15:54:37

Is there anything else that we can do to achieve this other than the native incremental refresh?

[Anonymous]

Hi @SaurarbhUpland ,

1. Save the initial query as a separate table:

2. Save subsequent updated queries as a table as well:

3. Append query:

4. Group by ID and keep the last row:

then expand the Data column:

If I misunderstood your question please feel free to contact me.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[SaurarbhUpland]

Hi @Anonymous 

Thanks for looking into the issue, However, I am not sure if this solution works when the second table is dynamically pulling the data from the web-based source based on the updated_at field, please find the use case below

1. We will fetch all the data by making the API call to /api/v1/items (without the delta parameters) this will be stored in the items query, lets say it returns all the records with IDs 1,2,3,4,5,6

2. Now based on the last updated_at record from the above table, we will make periodic queries to /api/v1/items?updated_at_gteq(delta parameter) now this may return IDs from the above query, new IDs or nothing at all, we want to merge the results of all the API calls so that we will always have items with all the IDs that were ever returned from the delta API calls so that the data in PBI is always updated

The solution you provided will lose the items if Table 2 changes with time, which will result in data loss for us.

Please let me know if this helps. Thanks!