Pulling data into Power Bi via API: Skipped questions at set up not added when answered post set up

User Support Data Management
, , , ,
20

Thanks. Are you sure that you tested it with AT FIRST no entries in the repeated questions, then established the API and THEN enter data in the repeated questions?

By the way, if I follow the recommended video (which I consulted several times before), during the get data procedure no “Navigator” windows pops up with SEPARATE tables.
image

21

I am not sure what this exercise/test was about, but I found out that the csv-file is only containing the “surface level” (but not the repeats).

Anyway, I created a completely new questionnaire, asking for numbers of four types of household members and then asking one or two repeated questions on each of them (four times “begin repeat” to “end repeat”.)

In the first two questionnaires, I left the number of two types blank (so no additional questions on them). Then I did the Power BI query and saw only data on those two used groups.

Then I filled out another questionnaire, this time saying 2 for one of the previously unused types and answered the repeated questions.

Again:

the additionally used types (and the repeated follow-up data) don’t appear, evon not when starting a completely new Power BI file and establishing the API again

the data of the repeated questions are hidden in “Lists” and there is no automatic split into main table and sub-tables

By the way: If I export the same data to excel and get data from excel in Power BI, everything is there (even in separate tables).

22

Here is an additional observation (not sure whether related): Looking at the table https://kf.kobotoolbox.org/#/forms, it says “1” under submissions for my repeat test form, although it has 3 (correctly shown in the form summary tab).

23

Here, do you mean you were able to collect the data as follows:

  • Suppose you have 2 repeat groups and some normal questions (say 3 normal questions i.e. these questions are not within the repeat group). You filled 3 normal questions but did not fill the 2 repeat group questions.
  • You then submitted the filled up form to the KoBoToolbox server.
  • Connected your KoBoToolbox server to the Power BI and was only able to see the 3 normal questions filled but did not see the repeat groups (at the moment).
  • You then again collected the data and this time filled up both the repeat groups as well as the normal questions and submitted them to the server.
  • You are now back tot he Power BI and then you refresh your page and expect that the response to the normal questions as well as the response to the repeat group should be shown in the Power BI.

Did i understand you correctly?

24

Yes, in principle, this is correct (see below for the slight variation with, I guess, no logical difference), just that I don’t expect the repeat group data popping up in Power BI automatically, but to be accessible / addable in the Transform Data preview (not losing the cleaning and modelling work by starting from scratch).

Just for information, here is the exact approach taken: I have built a small questionnaire asking for the number of boys and girls in a household and then asking two repeated questions for each boy and two different repeated questions for each girl. If I fill out and upload one or several forms with the number of girls always being zero (so that the repeated questions will not be asked), establish an API connection from Power BI (as per Janna’s video), fill out a few more forms now also answering the repeated question on girls, then the columns related to the repeated question on girls will NOT appear in Power BI. If I enter the URL used in a browser, all the fields are shown.

1 Like
25

OK, could you share with us a short video of how you are currently doing and what appears at your end. Maybe we would be able to follow you through the shared video and replicate it at our end.

26

Ok, I have sent a wetransfer link to KoBoToolbox Community Forum community+7c42b426d0b38b231707b7a805822b57@kobotoolbox.org

27

Not able to open it. Maybe you could upload the video to a youtube and then share the link in the community.

28

https://wetransfer.com/downloads/3c84815650310c838eaff4c3dac20a4420201105100950/a380a3

1 Like
29

I am able to download your file. Will view it and then get back to you.

30

Hi

Is no news bad news (i.e. no new insights generated)?

Kind regards
York

31

I’m sorry I didn’t follow the entire thread, but after using Kobotoolbox & power BI for nearly 5 years I can confirm that questions that have never been answered (due to skip logic) will not show up in Power BI through the API.

There are two workarounds to this:

Number one is to fill in one submission that answers all the questions. Once you set up the Power BI query, you can delete that submission.
In order to make it easier to fill this initial submission, you can disable all skip logic temporarily by editing the ‘required’ column in the form excel. Then once you have uploaded this submission, you can replace the form with the original excel file.

The second method is to manually add these column names into Power BI when you are setting up the query. Usually it’s this step (you have to add each field to the list of fields to expand):

= Table.ExpandRecordColumn(#TableName, Columnname, <List of fields>)

I don’t know how it works with repeat groups though, because I’ve never used it.

2 Likes
32

Thanks a lot for these hints.

Yes, submitting enough questionnaires so that all fields are filled at least once is the approach we followed so far (editing the ‘relevant’ column – not the ‘required’ column, I believe – is a really good idea to achieve this with just one submission).

However, the big question remains what to do if one needs to add another repeat loop to the questionnaire in a later revision. Manually adding these columns in PowerBI would indeed be a viable solution (as it would allow to keep all cleaning, modelling and chart definitions), but this is exactly what I try to find out: Is it really possible?

Anyone out there with enough experience re PowerBI who could tell?

1 Like
33

Yes, I think it’s possible from my experience with it.

1 Like
34

This might be because of the following:

  1. The JSONs produced by the API /assets/[asset_id]/data.json call (this is the API that PowerBI requests) contain only fields that have been populated for that particular response
  2. When PowerBI REST query and data transformation is created, PowerBI will create the transformation rules only for the fields that are available in the set of JSONs downloaded at that time (as expected)
  3. If new fields are made available in JSONs in subsequent updates of the data downloaded (clicking Update button in PowerBI), PowerBI will ignore these fields as it does not have registered transformation for them. Hence, the fields will look like “missing”

All above is by design and the workarounds can be as follows:

  1. As suggested by ks_1 or
  2. KoBo designers include all fields in all responses, regardless of whether there is data attached to the fields or not. This still does not resolve issue when new fields are added to the asset (form), like after several submissions you decide that you want age of the boys and girls, not just their names

Regards,
Alex

1 Like
35

Thanks so much, Alex!

It seems that I didn’t overlook anything and that the described behaviour is a matter of fact (fields filled in later [workaround possible] or added to the questionnaire later [no workaround] will remain “missing”, i.e. not be part of the transformation).

So, the decision whether or not to embark on Power BI as software to make use of the collected data seems to rely on whether I find some Power BI specialist who tells me that the transformation rules for such later added fields can be added in a "surgical intervention’ without having to start the cleaning, modelling and report building from scratch.

36

Dear all who you have spent brain on this challenge

Finally I found someone who knew the answer: Hard to say whether the problem should be regarded as one of Power BI or one of the kobotoolbox API, but there is a (pretty hidden) trick to activate fields in repeat loops which have been added to the form (or filled in for the first time) later than establishing the API query in Power BI for the first time.

  1. Go to Transform Data
  2. Make sure you have expanded one of the repeat loops
  3. Click the gear wheal besides “Expanded Column 1” in Applied Steps (at the right)
  4. Click “Load more” at the end of the appearing field list
  5. Activate the now visible new fields.
    Outwit PowerBI if API kobo data dont appear
    Outwit PowerBI if API kobo data dont appear560×743 132 KB

Thanks again to all who have helped to ask the right questions.

Bottom line: Power BI seems to be a valid tool even if your kobotoolbox questionnaire is still developing.

5 Likes
37

Hi @york_rff,

Thank you for sharing this wonderful trick! This should help the entire community! :clap: :clap: :clap:

Expecting your support to the community as usual! :star_struck:

38

Hi My friend ,

you know it dose not works today after the last update of kobo server, so please help me to do it in new features .

Thanks in advance

40

Welcome to the community, @hussamr! Have you gone through the steps as outlined in the post above?