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:

  1. WRT http://support.kobotoolbox.org/en/articles/592398-api-and-rest-services , get your API token
  2. Get your Form uid - e.g. visit your Project in kobotoolbox and it is embedded in the URL, between “/forms/” and “/summary”
  3. Construct the URL e.g. in Notepad, following the form: https://[kpi-url]/assets/[form-uid]/submissions/?format=json
  4. In Power BI Desktop, hit Get Data and choose a Web source.
  5. Choose the Advanced option. Enter the URL into the 1st URL parts textbox.
  6. Under HTTP Request Header, Choose Authorization in the drop-down, and next to that enter: Token [api-token].
  7. When prompted for Credentials, choose Anonymous
  8. If everything lines up, you should end up in the Power Query window, with a single List-type column. From the List Tools ribbon hit To Table, then OK
  9. Hit the Expand button (double arrow) in the header of Column1. Choose whatever fields you want returned.
  10. Rename columns and set data-types as you would normally in Power Query

I’m not a security expert, but I believe this to be fairly secure - the token is passed encrypted in the request header.

If you have a Power BI Pro account, you can set up scheduled refresh in the web service. Just check the option to Skip test connection.

3 Likes

Hi @mikehoneymf,

Welcome to the community and thank you for sharing your knowledge to the entire community! Expecting the same in the upcoming days too!

GOOD DAY!

Hi Kal,

Thanks! I’ve been really impressed with Kobo so far, and hope to use it for several projects.

But realistically as a kobo newbie I’m more likely to be asking questions than sharing knowledge :wink:

Mike

1 Like

Hello everyone!

Well fo rmy side i use the URL given by this website from UNHCR when putting the credencial of my (OCHA) account:
https://im.unhcr.org/kobosupport/

In this page you will find all the information needed and some tips too.

Have fun :slight_smile:

1 Like