Pull data from Kobo to Power BI

[scherrie]

I have read the articles and encounter the problem when I put in my user name and password I am being told “we could not authenticate with the credentials provided”. This is my login credential for kobo. What should I do?

My account is created under kobotoolbox.org. not humanitarian response. However, when I follow the instructions

and select the api for toolbox.org then select the api for submissions, the following screen appears:

. When I put in my credentials that is when I am told my credentionals could not be authenticated.

[stephanealoo]

Hi,
The issue is that somewhere during the process you forgot to change kobotoolbox.org as opposed to humanitarian server.

Stephane

[scherrie]

I got it fixed. Thank you. Just for clarification. Now that I have the specific form linked to Power BI, once additional entries are made in Kobo, will the dataset in Power BI be automatically updated?

[scherrie]

So now I have a challenge where the xml value is showing in the data set, but I need the corresponding choices value to show instead in the data set, so that the visualizations can be more informative. How is that achieved?

[mohan_n]

there must be a database where you have relation of xml values with choices. Use that in the power BI data model. I guess, that should help.

[scherrie]

That database would be the xls download export file. See attached.
Testa.xlsx (82.9 KB)

Please note that I linked Kobo to Power BI using Postman (i found an article on the forum here). However, the choices are not populating in Power BI, only the xml values. I would prefer not having to manually download the file, change the headers to english, and then import to Kobo. Is real time not an option?

[Kal_Lam]

Hi @scherrie,

Would you mind having a look at the post discussed previously, it should help you solve your issue:

Connecting Power BI to KoBo

The easiest way to connect Microsoft Power BI and view all non-public datasets in KoBo is by Step 1: Connecting to the following web url (https://kc.humanitarianresponse.info/api/v1/data?format=csv) then entering your KoBo username and password.You will receive a CSV file with a directory of all your datasets and their respective URL. [image] **Step 2: **Copy the URL of the dataset you need to connect to then add it again as a new Web source. [image] You can alternatively use the attached …

And this discussion should also help you out:

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 [image] Select: Anyone can view submissions made to…

And also this post discussed previously:

Kobo to Power BI - securely via API

I’ve been able to use the new kpi API to connect Kobo securely to Power BI and read submission results. I thought this might be of interest to others or could be improved on, so I’m sharing my steps below: WRT http://support.kobotoolbox.org/en/articles/592398-api-and-rest-services , get your API token Get your Form uid - e.g. visit your Project in kobotoolbox and it is embedded in the URL, between “/forms/” and “/summary” Construct the URL e.g. in Notepad, following the form: https://[kpi-url…

Have a great day!

[Ysr3322]

Dear @scherrie

Try this trick:
When you try to get the data form the web change the link as the following:
https://kc.humanitarianresponse.info/api/v1/data/XXXXXX?format=csv
change it to
https://kc.humanitarianresponse.info/api/v1/data/XXXXXX.xls
As a result, you will get the labels in the headers.

Best,

Yasser

[scherrie]

Thanks for replying Yasser. My challenge is that my account is set up in kobo toolbox.org not humanitarianresponse. As mentioned before I have tried all previous suggestions however when there is a multiple choice question, the xml value is what is showing up instead of the description/choice which was assigned the xml value.

I also note though if I download the xls file from kobo having changed the header to English, the actual choices instead of the xml value shows. I would hope that I would not have to download this file daily and do analytics and then just use power BI for visualization. Once again my form is associated to kobotoolbox.org not humanitarian response.

I look forward to hearing from someone who can help me.

[stephanealoo]

Hi @scherrie
Considering that the form is on Kobotoolbox I suggest you change this to kf.kobotoolbox.org/xxxxx

The challenge with the value labels on multiple response items has been an ongoing issue. To get this to work on my end, I have always ensured that the value label for the multiple responses is kept as the text in the values too. At the moment we do not have a solution but we can keep this open to see if other users have sorted it out.

Stephane

[scherrie]

Kf.kobotoolbox and not kc.kobotoolbox ?

[scherrie]

Hi, I followed what you suggested, and had the xml values reflect some of the choices. I replaced the form, then went to power bi. The choices are still not showing. I have highlighted 2 sections in the xls sheet for you to see.

See attached file:
Kobocommunity.xlsx (82.3 KB)

[Kal_Lam]

Hi @scherrie,

Would you mind dropping the use of / ( ) and see if it works. Generally these are the characters that are not supported for a variable name. I still see the use of the same in the highlighted section of the document that you shared:

Have a great day!

[scherrie]

Can they be replaced by _ or ; or ;?

[Kal_Lam]

Hi @scherrie,

If it’s a fresh deployment i would say YES.

Have a great day!

[imad1]

Dear All,
I seek your respectful support,
I was able to pull data from kobo toolbox form using URL web, but, unfortunately, the data still displaying as names not labels?!!

[stephanealoo]

Hi @imad1
Welcome to the community forum. Could you please have a look at the topic that has been discussed below and see if it resolves your issue?

Kobo toolbox json api

The kobo json api is very not very clear and I understand people might be struggling to use in with their applications. After months of struggling and a lot of guess work i decided to write what I have been able to achieve. Before you continue I cannot guarantee that it works for everyone. Also I have a self hosted instance of kobo so the url and requirements might be different. Getting ready I am assuming that you have your kobo instance set up so I will not go over that part. If you ha…

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 [image] Select: Anyone can view submissions made to…

Stephane

[etan]

Salut,
j’ai connecté mon projet kobo à power bi! Mon projet effectue une collecte de données de façon hebdomadaire. Le problème que je rencontre actuellement est que je n’arrive pas à actualiser les visuels que j’ai créé dans power bi malgré qu’il y a eu de nouveaux ajouts. Je reçois des messages d’échecs suivants: Chargement bloqué par des échecs avec d’autres requêtes; la clé ne correspondait à aucune ligne dans la table.

[etan]

Pull data from Kobo to Power BI

[IMG_20220909_184451] Salut, j’ai connecté mon projet kobo à power bi! Mon projet effectue une collecte de données de façon hebdomadaire. Le problème que je rencontre actuellement est que je n’arrive pas à actualiser les visuels que j’ai créé dans power bi malgré qu’il y a eu de nouveaux ajouts. Je reçois des messages d’échecs suivants: Chargement bloqué par des échecs avec d’autres requêtes; la clé ne correspondait à aucune ligne dans la table.

[Kal_Lam]

@etan, could you make a fresh connection to see if this solves your issue?