For capturing baseline data and recurrent updates, we have created a questionnaire (xlsform) which has become quite complex by now. There is a lot of skip logic and repeats – for instance, capturing either data on a school or data on a ECD centre, as well as asking for the number of preprimary classes and then for enrolment numbers of each of them.
Janna’s wonderful youtube tutorial (https://www.youtube.com/watch?v=mDokTnjM6Fo&t=518s) worked perfectly to get the data from kf.kobotoolbox.org via API into Power BI – thanks so much!
However, as a bloody beginner in Power BI, I wonder whether the following problem is just due to lack of Power BI skills or whether there is a problem in the way Power BI is communicating with kobotoolbox when the power query is done.
Two symptoms bother me most:
Missing fields: Due to the many skip logic patterns (“tree” with many “branches”), I would have to fill out many, many forms to ensure that each and every question is answered at least once (and doesn’t remain skipped/empty in ALL questionnaires). For testing purposes, I filled only a few and it seems that only those fields/columns appear in Power BI which were not empty in the moment when I did the first power query (=when I connected via API as per youtube video). If I fill out another form and answer so far unused questions, the counter of questionnaires increases, but the new questions don’t appear in the Power BI table. Do I overlook some refresh function or something like that?
Just one table: If I understand the Power BI tutorials correctly, repeated questions from kobotoolbox are shown in separate tables on the modelling page. But this is not the case for our questionnaire: The API connection brings all the columns into just ONE table. While I believe that the reference feature allows to extract columns into new tables, it looks like an awful lot of work – which by the way is meaningless if problem #1 (missing columns) is not solved beforehand. Any idea how to do the “import” differently?