How to add new data to the existing table when using the max id of that table to query the API

[aukev]

What I`m trying to achieve is the following. I need to get data from the API. The API has over 100000 results and gets new results daily (transaction data). So instead of getting all the results every day, I would like to grab only the new results. The API supports this. So I could say, get all results where the id is larger then xx.

I`m using List.Max to get the highest id of the initial data. Let's call this query "AllData". I.e. this is 0 in the beginning but could be anything.

My idea is then to set up a new query, named "ImportData". This query would grab the highest id from "AllData". Query the API and get the latest data.

So far I got this working.

Now the issue is combining the new and old data in a way that the next day I can grab the new max id. Grab the new data and combine this with the original data and the data from yesterday.

What I have so far (simplified) you can see below but this gives me a cyclic reference error.

Additionally, I would like the queries to also work on the online power bi service.

Some help/advice would be great 🙂

----------------------------------------------------------

//Table name: AllData

let

Source = //Original Data. Could be just one row with initial id,

AllData = Table.Combine({ImportData, AllData})

in

AllData

------------------------------------------------------

//Table name: ImportData

//Import fresh data from API

let

OriginalData= DataTable, //Table with initial data

MaxId = List.Max(OriginalData[ID]),

NewData = //API with max id

in

NewData

------------------------------------------------------------

[Greg_Deckler]

Are you using Power BI Premium? @ImkeF might have some thoughts on this, she has done a ton around Power Query and API access.

[aukev]

No, I have power bi pro though. But ideally I`d also like it to work on Power BI desktop if possible.

[ImkeF]

Power BI is not designed to persist old data. There are a couple of workarounds though:

https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/

---

[aukev]

Thanks @ImkeF . Thanks to @Greg_Deckler 's comment I mentioned to find that page already in your post history. It seems though that the refresh enabled issue in PB service isn't fixed yet.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/20579641-exclude-a-table-from-ref...