question

StevenDixon-2722 avatar image
0 Votes"
StevenDixon-2722 asked emizhang-msft commented

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

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


power-query-not-supportedoffice-excel-itpro
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

StevenDixon-2722 avatar image
0 Votes"
StevenDixon-2722 answered emizhang-msft commented

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"
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @StevenDixon-2722,
Thanks for sharing solution here. Other partners who read the forums with the same issue can get more information from the correct result.
Thank you.

0 Votes 0 ·