Managing repeat groups with Power BI

Hi to everyone, a question: I followed Janna’s steps and now my Kobo form is connected to Power BI, but I have grouped repeated questions, and as for the excel data analyser my excel file has 4 sheets and only the first was imported. Could someone have a solution?

Welcome back to the community @luca_bolognini! Are you trying to get the same in Power BI or are you trying to get the same in xlsanalyser?

@Kal_Lam, sorry, I’m trying in Power BI… :yum:

Hello guys, another question on this topic: I added an additional question in my kobo form, I refreshed the data in Power BI, but this new question didn’t appear. Why? I need to repeat the procedure to connect kobo and PBI? :thinking:

…and also: each record in kobo was doubled in PBI. I noticed that geolocation was splitted in 2 row for each ID: one whit lat and one whit long. Hou can I solve it?

Hi @luca_bolognini

This is a known issue where PowerBi would not add any additional questions added to the system.

When you download the XLS version of the files, do you find the data in duplicates?

This is by design and you can only get them as a single column when you make the amendments withing PowerBi to read this data and combine the columns.

Stephane

1 Like

Thank you @stephanealoo ! I have more Kobo forms and I need to integrate them all together in order to have a clear idea about the working progress from different forms. No, when I download the file the data are not in duplicates.

This is quite off-topic from this thread, it would be better if you started a new thread.

One possible solution would be using the Power Query Editor (Home ribbon, Transform data button).

Select all the other columns (besides lat and lon), then choose Group By. In the Group By pop-up choose Advanced. Choose Add aggregation, then choose New column name = lat, Operation = Max and Column = lat. Repeat for lon. You can delete or ignore the default Count aggregation.

It should then summarise the pairs of rows you are seeing into one, with populated lat and lon values.

If I’ve misunderstood your requirements, please start a new thread and post more info.

2 Likes

Thank you @mikehoneymf!
The Kobo Toolbox is very useful tool, but the community behind is absolutely amazing!
:v:

2 Likes

Try this, in the advanced editor of your query…

2 Likes

Dear @nca_hum_div many thanks, I’ll try and I’ll keep you informed!

2 Likes

There is a solution to the “known issue” – see: Pulling data into Power Bi via API: Skipped questions at set up not added when answered post set up - User Support / Data Management - KoBoToolbox Community Forum

[I added this remark, because I received an email pointing at this thread today – now I see that the thread is 3 months old…?]

1 Like

Thank you @york_rff :+1:

1 Like