cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
waeltken
Helper I
Helper I

Power Query Web.Contents( Query Parameter requesting null values from API not working

To the PBI Community,

 

I've been dealing with an interesting problem pulling records from a REST API source. One of the API query parameters is archive_reason_id. In the remote DB, this field is empty when a candidate has not yet been archived, i.e. is still an active applicant. 

 

When I want to request only blank or null values directly in Postman the URL would read something like this 

GET 'https://api.baseapi.com/applications?archive_reason_id=' and it works perfectly fine, returning only records where archive_reason_id is blank or empty. 
 
When I try to recreate this API call in Power Query I'm using Web.Contents( and I've tried a number of variations on this, coding the "?archive_reason_id=" into the base URL and alternatively also into the Query parameter. I've tried 
Query = [
archive_reason_id = "" --> returned records contain both blank and non-blank, so parameter does not work
archive_reason_id = {""} --> returned records contain both blank and non-blank, so parameter does not work
archive_reason_id = " " --> returns no records
archive_reason_id = null --> this results in an error of cannot convert null value to text
]
 
Again, this works just fine with Postman, sending the 'https://api.baseapi.com/applications?archive_reason_id='. None of the first 100 records has an archive reason. All are blank / null.
 
I've been reviewing the M language and function documentation:
 
and then there are some articles by Chris Webb which I thought might help, but I'm not able to distill the needed insight from it
 
1. Do you have any suggestions or pointers?
2  Is there a way to audit what the resulting URL is that Power Query is producing and calling based on the Power Query function?
3. Is there are way to inverse the parameter in Web.Contents( to list all the Archive_Reason_Id which should not be returned like 

archive_reason_id <> {"Hired","Applied", "Round 1 Interview"}
 
Any help is truly appreciated. 
 
Regards,
 
 
Henrik
 
2 REPLIES 2
waeltken
Helper I
Helper I

Hey Phil,

 

thank you for your note - yes, my preference would also be to download the whole set and filter inside Power Query. This does however run me into performance problems with the API that I'm requesting from. The full table is approx 150,000 records and I'd rather only download the relevant records in the first place.  Another factor is that I have to run a nested API call from witihin the results as a custom function on a per-row basis which uses two field values as inputs. And unlike the table download which delivers in pages of 100 records, the individual calls are capped at 10/sec, which causes a significant delay.  Along those lines, is there a way, some code or add-on to Power Query which provides the user some more insight on processing durations during the different steps undertaken by the constructed query and the total records processed? Some sort of a counter etc. which can be instituted so as to help pinpoint the biggest drags on query performance?

Thank you for the tip with Fiddler. I had heard of the tool before but never got a chance to use it. I will check it out. Where does it benefit your workflow most?

 

I've reached out to the vendor to figure out if there are other attributes which can be used as a proxy for "no archive reason".

 

Regards,

 

 

Henrik

PhilipTreacy
Super User
Super User

Hi @waeltken 

 

Can't you get all records then just filter the results to only show you the records with archive_reason_id  blank?

 

You could use a proxy like Fiddler to check what calls are being sent to the API.

 

You can't 'inverse' 'the parameter query as it's the API that dictates how data must be sent.  Unless the API allows you to say 'send me all records except Hired, Applied etc' you can't do it.

 

Regards

 

Phil

 

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors