EXCEL Webconnect: Is there a way to filter the XLS (or JSON) on the server before importing into Excel?

User Support API
, ,
1

Hi all,

We’re using Excel/Power M to connect to a form and pull all submissions into a table. This form contains many, many needs assessments and with Power M each refresh downloads all submissions and takes quite some time for users. Obviously the submissions can be filtered after the query has refreshed, but is there a way to write the Power M query to filter the submissions (between two dates) before downloading them to Excel?

We currently use Excel to connect to the XLS. Doing some research, it appears that maybe it’s possible to filter by connecting to the JSON (Filtering API query results by date) however when using Power M I can’t seem to reformat the records into a table (see screenshot). It’s also not clear if it’s possible to filter between two dates with this method.

Any support would be really, really appreciated! I’m a bit out of my depth here, so thanks in advance.

Screenshot 2021-09-06 191442
Screenshot 2021-09-06 191442762×397 8.66 KB

2

Hi there, yes it’s great to use the JSON API to bring results into Power BI - very flexible.

  1. To expand the list of records (as in your screenshot) then just follow the instructions in step 4 here: KoboSupport (there’s an “expand” button in the top right of your list, just off the edge of the screenshot)
  2. You can filter by multiple parameters just by putting & in between them. The Mongo documentation also has more advanced ways of combining filters but I have thus far taken the simple approach of just doing multiple queries (one greater than and one less than)
3

Hi Nat, thanks for the reply.

I have managed to expand the data, converting the list of records into a table. However it’s your step 2 that I’m having difficulty with.

As stated, I don’t want to filter in the Power Query editor as that is “too late” if that makes sense. By filtering in the Power Query editor, the client (in my case Excel, not PowerBI), still downloads all the records). I’m confident there is a method in Power Query using Web.Contents() to filter the records that are downloaded to Excel.

5

OK - Sorry all for the multiple posts.

To simplify my issue. I need to filter the submissions that I GET from my Kobo form using a date field. I can see this is possible with the API and, indeed, I have managed to achieve this using POSTMAN. The following request returns exactly as needed:

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"}}] }

However, I need this to happen within Excel using Power Query. From the resources posted above, it ought to be possible to replicate this request within Power Query using the Advanced Editor using the Web.Contents() function. Yet, I am unable to succeed in doing this. My best attempt in Power Query Advanced editor is as follows:

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

Unfortunately, this does not filter successfully. Any ideas?

Best,

6

Cracked it!

As expected, I was entering the Power Query syntax wrong for the Web.Contents() function. 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 XXXXX"]])),
     #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"
3 Likes