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

[waeltken]

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:

https://docs.microsoft.com/en-us/powerquery-m/web-contents
https://docs.microsoft.com/en-us/powerquery-m/m-spec-values 

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

https://blog.crossjoin.co.uk/2018/03/05/character-escape-sequences-in-m/

https://blog.crossjoin.co.uk/2021/01/10/handling-multiple-url-query-parameters-with-the-same-name-us...

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

[waeltken]

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]

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