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

Steven Dixon 96 Reputation points
2021-09-07T17:59:47.887+00:00

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.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
    2021-09-07T19:28:27.377+00:00

    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"
    

0 additional answers

Sort by: Most helpful