Simple Connector for Power BI (Excel Power Query) to get labels from Kobotoolbox Data

Hi all,
I have created a Power BI Template that connects a KoboToolbox Form to Power BI in a simple way. I think that it is simpler waythat the one HERE, I never managed to make it work…Like others I always got a Json Problem)

The file is HERE

It will get you the data with LABEL (Human Readable) in the DEFAULT language of your form.

The data needs to be public for this to work. Thus you need to go to your form and go to SETTINGS–>SHARING


Select: Anyone can view submissions made to this form

I USE THIS METHOD ONLY FOR NON-SENSITIVE DATA AS YOU NEED TO SHARE THE DATA FOR THIS TO WORK!!!

Then when you open it it gives an Explanation of whta it does:
image
Then you need to insert Kobo_Instance, the Kobo_Account that created the form (not necessarity account your) and the form ID:
image
Curently you can choose only 2 instances, if anyone is interested in others, please let me know…
image

The template gives you also a CALENDAR TABLE (From Curbal.com)
Fill up the parameters ( Starting date of your calendar) following the instructions:
image
EXAMPLE:


Then clik LOAD ( or edit and Load if you need to edit your query)
You will get a Power Bi File that will contain 2 Queries:
image
You can always go to the Query editor and change them:
image
As you can see the entered info for the parameters is there:
image
So it will be the data (KOBO API) ( labels of the Default Language, in my case Portuguese)
ADDITIONALLY…Based on @bareta nice help, I have added a colum with the edit link for ech of the submission. You can create a table that will open them (if you have the permissions)
Like this:
image

(Install Json Viewer for Chrome so the Web URL is clickable)
Should you not be logged in Kobotoolbox or not have prermissions (“Edit Submissions”) you will get this message:

I hope this is useful for the community!
Regards

3 Likes

Hi @iker.montes,

Thank you for sharing this wonderful resource in the forum. It should help and benefit the entire community users. Expecting the same in the upcoming days!

Have a great day!

Hi @Kal_Lam and @iker.montes,
I got this working this morning, thank you. I had to edit the query so that it hooked up to kc.kobotoolbox.org. But once I got that done, it loaded the data.
Thank you!

Are you able to suggest how to link to Power BI when we have sensitive data that we don’t want to share publicly? I think it could be through the REST Services, but if either of you can advise, would really appreciate it!

Speak soon,
Janna

If you ket me know the address used for the kc.kobotoolbox.org, I can make a dual one for both . I actually tried but no success ( I dont use kc.kobotoolbox.org myself)
It is just a case of having something similar to …

https://kobo.humanitarianresponse.info/assets/FORMID/submissions/SUBMID/edit/?return_url=false

It would be great as more people could use it!

About getting the labes in a private way … I would love to have it also, but no luck yet.

2 Likes

I think it’s literally the exact same url construct, I believe… Just replace kobo.humanitarianresponse.info with kc.kobotoolbox.org

If you want me to send you my form id, let me know (public test data, nothing sensitive). If you want to test it out.

1 Like

Sure, please share the URL path…
I tried:

https://kc.kobotoolbox.org/ikermontes/reports/aZPyLmRWbCDZT554JcNp2b/export.csv
and
https://kf.kobotoolbox.org/ikermontes/reports/aZPyLmRWbCDZT554JcNp2b/export.csv

both times same error

DataSource.Error: Web.Contents failed to get contents from ‘https://kc.kobotoolbox.org/ikermontes/reports/aZPyLmRWbCDZT554JcNp2b/export.csv’ (404): NOT FOUND

If I manage to make it works I’ll create another parameter to choose the instance, simpler for people than editing the query!

Hi @iker.montes,

Try my form ID and userid:
user: jannadata
form id: ady8SUPNiuBFaZ6xUWfSFn

and the url is kc.kobotoolbox.org

so the full url becomes: https://kc.kobotoolbox.org/jannadata/reports/ady8SUPNiuBFaZ6xUWfSFn/export.csv

Hope that helps! I think it would be really useful to put the selector, whether people use the humanitarian version or the “everyone else” version of kobotoolbox!

Janna

1 Like

Hi @janna,
THnkas for sharing the data, I made it work.
TUrns out that my data is not reachable (thus Error404?)even if it is shared. Maybe because it is a very old form.
Your works though. Then I have refreshed the file in the link and now we can Connect to both instances of Kobo. (munanitarian and “Everyone Else”…

1 Like

Hello everyone,

Just to mention, i am using the OCHA server and in this case if much easier to use this Website:
https://im.unhcr.org/kobosupport/#form

It also allow data to be private :wink:

1 Like

Hi quentin,
I used this also in the past but you get the names (AKA: machine code) rigth?
Whereas with the link I provided you get the label ( AKA, this I can read)
EX, in my forms countries get number in alphabetical order (…1,2,3), whereas I need names (Angola, Benagadesh…)
I could do a lot of manipulation in PBI To change this… but I dont want…Lazy person.
The unlucky bit is that I need to get my data public…

@Quentin Do you know a way of getting the labels ( even for different languages) been private…that would be the best way forward?

True, it’s only the names
I usuelly try to have names that have a sense both in the survey and choice sheets in order to understand the data and in PowerBi i only show the data and writte down the titles or transform the labels.

However there could be a way integrating your XLS in your powerBi model and using some DAX, however i’m also too lazy to do so… :wink:

Hi @iker.montes

First of all thank you for your answers and posts, it has been very helpful.
I would like to ask if you can still getting data from the URL:

https://kc.humanitarianresponse.info/ “USERNAME” /reports/“FORM ID” /export.csv

As today, i am always receiving error 404, i have checked my form is public and i dont have set any specific language. I was searching another way to get an export from API, as we can get it from the export function in the platform and i always end reaching this solution.

Can you confirm the url is still working?

Regards,

No it does not.!


get the same message…
@Kal_Lam and @stephanealoo can you have a look at why this is happeninig?
thanks

@iker.montes, we are looking at this issue and will reach you back when we resolve it.

1 Like

dear @Kal_Lam , is there any improvements on this?
It would be great to some of us, using this way to access data, if we could get this fixed:-)
Regards

1 Like

Sorry @iker.montes it’s still not fixed. But we will let you know when it’s fixed.

1 Like

Hi @Kal_Lam any update on this fix? Still struggling with labels…

@dwangola, have you tried this API as discussed in our other post?

It should now solve your issue.

HI @Kal_Lam, I’ve tried this method, but all it shows is a list of urls for “user”:“https://kc.humanitarianresponse.info/api/v1/user “forms:”“https://kc.humanitarianresponse.info/api/v1/forms””"
“submissions:”“https://kc.humanitarianresponse.info/api/v1/submissions”"" , etc.

Using json option worked for me