Power Query and the Kobo Toolbox API: Using Web.Contents() to filter data returned to Excel

Question

Dear Stack Overflowers!

The NGO I am working for has a need to connect their Project Managers to Kobo Needs Assessment Survey data directly through Excel. This is easy enough. However, this Survey contains thousands of submissions/data rows and the time taken to refresh the connection is too long as each row must be downloaded to Excel before being filtered.

The KOBO Rest API does allow for queries to filter requests (https://kc.humanitarianresponse.info/api/v1/data) and I have achieved the desired results in POSTMAN.

I understand it is possible to use the Advanced Editor in Power Query with the Web.Contents() function to filter and achieve the desired effect, however I have thus far failed in writing the correct syntax. Could anybody have a look and tell me what I'm doing wrong?

The query that works correctly in POSTMAN (when using a Header/Authorization token):

https://kc.humanitarianresponse.info/api/v1/data/814220?query={"$and": [{"assessor_details/date_of_assessment": {"$gte": "2021-08-01"}},{"assessor_details/date_of_assessment": {"$lt": "2021-09-01"}}] }

The Power Query Advanced Editor Syntax that doesn't cause an error, but doesn't actually filter anything!:

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

Answer 1

As expected, the Power Query syntax for Web.Contents() was incorrect.

The correct syntax should be:

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 XXXXXX"]])),
     #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
    
in
    #"Converted to Table"