Only query new records

[JamesLeach]

Hello, all! 

I have a dataset pulled from a number of xml files in an Azure blob.   In that dataset are some IP addresses that I am querying location data for using a web API.  A lot of IP addresses, really, and that's part of the issue.  It's pretty wasteful to query all of these addresses again on a refresh so I'm trying to figure out a way to keep the results of the previous query. 

I have my function that queries the data using the API.  I copied all of the results to a manual (archive) table and then modified my function to check the archive table to see if that IP address exists.  If so, I use the data from the archive table, if not I query the data via the API. 

This works okay, until I try to add new results to that archive table.  I've not been successful in adding data to the archive table without creating some sort of issue with Power BI as it's a pretty cyclical situation:  a check to see if a record exists in the archive table is made, it's false so the web api is queried.  But, if that record is added to the archive table somehow then the check would be true...   Power BI doesn't seem to be handling what I'm trying to do well which means I'm not doing it right, as far as Power BI is concerned...  

Does anyone have any suggestions as to how to handle this?   Basically, I'm trying to have persistent table that I can add rows to if there isn't already a matching record.

[Greg_Deckler · Accepted solution]

@JamesLeach Honestly there is no great solution for this. I think @ImkeF (biaccountant.com) wrote a blog article on this that probably was the "cleanest" but there's really no "good" way.

Seems like what you want is incremental refresh though, which Power BI has but not sure how that might work exactly with the API you are calling.

[JamesLeach]

Hey Greg,

Thanks for the response! I was afraid the news was going to be something like that.   I couldn't find the blog post you referenced but I'm pretty sure this is the thread where it's discussed.   I've actually used that in the past querying and API where I had to pull data page by page...

Incremental refresh isn't a good option in this case, at least with the API data but, since there are many ways to handle things, I decided to create an Azure function that acts as a 'middleman' between Power BI and the Web API (that I pay for based on queries).   The function queries Azure table storage to see if it has a record and if not, queries the API and then writes it to the table storage, returning the appropriate data, either way.  

Thanks again!  

Be well,
James