Web.Contents() : Power Query to Kobo Toolbox REST API to filter by date

Question

Hi all,

Quite a specific problem here. I need to connect to a Kobo Toolbox dataset via Power Query in Excel. Due to the size of the dataset, I need the Power Query to filter by a date column before downloading all the records to Excel.

Looking at the REST API documentation (https://kc.humanitarianresponse.info/api/v1/data) it is possible to filter and indeed I have managed this by using the following syntax in POSTMAN.

curl --location -g --request GET 'https://kc.humanitarianresponse.info/api/v1/data/814220?query={"$and": [{"assessor_details/date_of_assessment": {"$gte": "2021-08-20"}},{"assessor_details/date_of_assessment": {"$lt": "2021-09-01"}}] }' \  
--header 'Authorization: token XXXX'  

I understand that I can "convert" this query into Power Query by using the Web.Contents() function, however I'm having no success. The following Power Query Syntax in Advanced Editor does not successfully filter.

let  
Source = Json.Document(Web.Contents("https://kc.humanitarianresponse.info/api/v1/data/814220",[Query="{""$and"": [{""assessor_details/date_of_assessment"": {""$gte"": ""2021-08-30""}},{""assessor_details/date_of_assessment"": {""$lt"": ""2021-09-01""}}] }",Headers=[Authorization="token XXXXXX"]])),  
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),  
in  
    #"Converted to Table"  

Any ideas? Thanks in advance! @ChristopherW-MSFT

P.S. Shared a screenshot of the Kobo REST API documentation.

Answer 1

Cracked it!

I was, as expected, entering the Web.Contets() syntax incorrectly.

The correct syntax is:

let
 Source = Json.Document(Web.Contents("https://kc.humanitarianresponse.info/api/v1/data/814220",[Query=[query="{""$and"": [{""assessor_details/date_of_assessment"": {""$gte"": ""2021-09-01""}},{""assessor_details/date_of_assessment"": {""$lte"": ""2021-09-03""}}] }"],Headers=[Authorization="token XXXXXXX"]])),
     #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in
    #"Converted to Table"