cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Only query new records

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Only query new records

@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.


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

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
Highlighted
Super User IV
Super User IV

Re: Only query new records

@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.


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

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Frequent Visitor

Re: Only query new records

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

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors