All columns do not appear when connecting to powerbi

User Support Data Management
14

Hi all, have been battling with this also and I think there are 3 parts to this business problem of setting up PowerBI integration before all the possible variations have been submitted - partly to do with the way KoBo behaves and partly to do with the way Power BI behaves. FYI I use JSON format when connecting to the KoBo API.

  1. The original issue identified by @tom.joseph in the very first post above whereby the KoBo API only returns data for fields that have been populated in the submissions. E.g. if I have a form with 3 (non-mandatory) questions, and my first submission only has answers to 2 of those questions, then only those two questions will be returned as fields in the JSON - e.g. in my form below had 3 questions breakfast, rain and new_question which all displayed onscreen, but as I did not answer new_question there is no data in the JSON from the API v2 (same behaviour in API v1) so when I first connect to PBI it is as if that question did not exist and PBI does not create a column for it.
   _id: 32356,
   _notes: [ ],
   _validation_status: { },
   _uuid: "b1ea757b-a634-487d-a5f0-a5227661788a",
   _tags: [ ],
   rain: "no",
   _submitted_by: null,
   _xform_id_string: "af8Zwtsc6h9AW2UhH8qpnA",
   breakfast: "Weetbix",
   meta/instanceID: "uuid:b1ea757b-a634-487d-a5f0-a5227661788a",
   formhub/uuid: "a56ee09be23b488696ab37b3fc7b4f5a",
   end: "2021-03-25T23:48:09.211+13:00",
   _submission_time: "2021-03-25T10:48:10",
   _attachments: [ ],
   start: "2021-03-25T23:48:02.617+13:00",
   _geolocation: [
   null,
   null
   ],
   _status: "submitted_via_web",
   __version__: "vzmZ97h7GNEVEgko7yj2Qj"
   }

This is something that can only be solved on the KoBo side - there is no way for Power BI to know about any data that isn’t exposed via the API! Possible ways to solve this business problem:

  • figure out a way to get a list of all possible fields and then manually code them as columns in Power BI (which can get complicated if nested groups/repeats etc.) - particularly because as you say above, in the API the fieldnames are returned with group prefixes, and I can’t find a way to turn this off in the data API (only the Excel export/download)
  • KoBo enhancement to add a toggle in the API to request the JSON to always return fields even if empty for a given record (which would massively increase the size of the data file if there are lots of empty fields) - this is what @tom.joseph is requesting here.
1 Like