OK, so do you mean you are not able to see only the data from repeat groups?
I tried to confirm. And indeed, it exactly looks like this: whatever questions are between repeat loops, are not shown.
Here are the lines from what I see after the csv export (as you described it above), followed by the respective lines from the xlsform (the term “Grade” comes through a variable for country-specific terminology):
Number of ${term_grade} 3 classes:
Number of ${term_grade} 2 classes:
Number of ${term_grade} 1 classes:
integer school_classes_g3 Number of ${term_grade} 3 classes:
begin repeat school_class_names_g3 ${term_grade} 3 classes names
calculate counter_names_g3
text school_class_name_g3 Name of ${term_grade} 3 class #${counter_names_g3}
end repeat
integer school_classes_g2 Number of ${term_grade} 2 classes:
begin repeat school_class_names_g2 ${term_grade} 2 classes names
calculate counter_names_g2
text school_class_name_g2 Name of ${term_grade} 2 class #${counter_names_g2}
end repeat
integer school_classes_g1 Number of ${term_grade} 1 classes:
begin repeat school_class_names_g1 ${term_grade} 1 classes names
calculate counter_names_g1
text school_class_name_g1 Name of ${term_grade} 1 class #${counter_names_g1}
end repeat
So your concern is that you are able to update/refresh the data if it does not have a repeat group but in cases where you have a repeat group data it is not showing in Power BI?
Yes, the problem is that if questions inside a repeat group get not filled before using the API for the first time, later use of these questions will be ignored in the power query (or even in a csv export).
I tried with the following url (with one of my dummy project) and it worked for me:
https://kc.kobotoolbox.org/api/v1/data/545827.xlsx
Maybe if you wish, you could make some dummy entries as well:
https://ee.kobotoolbox.org/x/w2cfGvN8
I could update the records (for both the household level as well as the roster information) once i press the refresh button.
Maybe this video tutorial prepared by @janna would help you solve your issue. Though the video pulls the data from an Excel file, you could still connect your data with the KoBoToolbox server:
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.
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).
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).
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?
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
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.
Ok, I have sent a wetransfer link to KoBoToolbox Community Forum community+7c42b426d0b38b231707b7a805822b57@kobotoolbox.org
Not able to open it. Maybe you could upload the video to a youtube and then share the link in the community.
I am able to download your file. Will view it and then get back to you.
Hi
Is no news bad news (i.e. no new insights generated)?
Kind regards
York
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
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
Yes, I think it’s possible from my experience with it.
1 Like
This might be because of the following:
- 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
- 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)
- 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:
- As suggested by ks_1 or
- 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
