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.