Connecting Kobo Toolbox to PowerBI

If you are creating a Power BI dashboard for use in an operation, there is a good chance that at least some of the data you will be visualizing will come from Kobo. This tutorial explains how to connect to and load Kobo Toolbox data in Power BI via an API connection.

Getting Started

1. Sign into your IFRC Kobo Toolbox account online. If you have never accessed the IFRC Kobo, you can sign up for an account here. Be sure to use your Red Cross email address- any other email provider will be rejected. 
2. Open up a blank text file or Word document on your computer. There are a few components that go into the API call, and we will use this document to record each of them.
3. On the blank text file, write or copy/paste the following:

• URL: kobonew.ifrc.org
• API token: See steps for getting this below
• Form UID: See steps for getting this below
• URL Connecting Kobo Toolbox to PowerBI: https://kobonew.ifrc.org/assets/[form-uid]/?format=json

Getting the API Token

1. Go to https://kobonew.ifrc.org/token/?format=json in your browser. (Again, make sure you are signed into your IFRC Kobo server account).
2. Your API token will appear on the screen. Copy the letters and numbers within the quotation marks after the colon. Do not copy the part that says “token”: or the brackets. For example, if this is your token, the part that you should copy is highlighted in yellow—{“token”:”12345678qwertyuiop”}. Paste that into your text file/Word doc next to “Your API token”.

Getting the Form UID

1. Visit your project in Kobo Toolbox
2. Look at the URL.
3. The form UID is displayed between “/forms” and “/summary”. 
4. Copy this and paste it on your text file/Word doc next to “Your form UID”.

Construct URL for Connecting Kobo Toolbox to PowerBI

On your text file/Word doc, you will see a URL next to “Your URL for connecting Kobo Toolbox to Power BI”. Replace the text `[form-uid]` with the form UID you obtained in the previous step.

Connecting to Data in PowerBI

1. Open up a Power BI window and go to Get Data.
2. Choose Web as your data source.
3. In the From Web dialog box that appears, select ‘Advanced’.
4. Copy the URL that you constructed in the previous step and paste it in the field under ‘URL parts’. 
5. In the field under ‘HTTP request header parameters (optional)’, add the following:
   • Field on the left, write: Authorization
   • Field on the right, write: Token [Your API token]. For example:
     1.  Token 12345678qwertyuiop
     2. Note: There should be one space between ‘Token’ and your API token.
6. Press OK
7. The Power Query Editor should now open, and you will see a window that looks like the following. Data has been imported as a .json list and you will need to convert it to a table, so press the ‘To Table’ button in the top right corner.

8. You will now see that the List has become one column. Click on the expand icon in Column 1.
9. Under the list of all the fields, uncheck the box that says, “Use original column name as prefix”.
10. Now your data is ready to transform!

Troubleshooting

My data looks like a row and not a list

If the above steps return a single row of form data (as below) as opposed to a list of submission data (as above) try using the below steps to pull in the submission data.

1. Using the same form UID and API token from above, use this link when you Get Data → Web
2. Select the Advanced option and type Authorization into the, “HTTP request header parameters (optional)” dialogue and Token [your API token here] into the dialogue box to the right.

3. Under the list of all the fields, uncheck the box that says, “Use original column name as prefix”.
4. Now your data is ready to transform!

Previous Kobo data is no longer loading into Power BI after connecting to a new Kobo survey

There are two ways to connect to Kobo data in Power BI. One is directly via the API as noted in the steps above. The other way, documented here is by creating synchronous links directly to a .csv or .xlsx export created in Kobo by setting up a download. 

If your previous data was connected through a synchronous .csv link, then you may have logged into Kobo via Power BI using your user name and password as opposed to your API token. This is causing confusion in your global permissions and each time you login into one of these forms, you will get kicked out of the other. To fix this:

1. Change the connection for both Kobo surveys to use the same method. 
2. Update your connection to the synchronous .csv link to login using the same “Authorization” Token [my API token here] credentials on the advanced Web connect dialogue box instead of the basic username / password route. 

Practice

If you don’t already have a form deployed to kobonew.ifrc.org, you will need to first upload and deploy a form with which to test. If you do not have a form at the ready, you can download this example form to practice with.

1. Download the XLSForm and upload it to Kobo. 
2. Deploy the form.
3. Submit a handful of entries so you have test data. 
4. Follow the steps above to load that data into Power BI.