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

Steven Dixon 96 Reputation points

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.

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),  
    #"Converted to Table"  

Any ideas? Thanks in advance! @ChristopherW-MSFT

P.S. Shared a screenshot of the Kobo REST API documentation.129945-screenshot-2021-09-07-131708.png

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
37,165 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,672 questions
0 comments No comments
{count} votes

Accepted answer
  1. Steven Dixon 96 Reputation points

    Cracked it!

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

    The correct syntax is:

     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)
        #"Converted to Table"

0 additional answers

Sort by: Most helpful