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]/assets/[form-uid]/submissions/?format=json
- In Power BI Desktop, hit Get Data and choose a Web source.
- Choose the Advanced option. Enter the URL into the 1st URL parts textbox.
- Under HTTP Request Header, Choose Authorization in the drop-down, and next to that enter: Token [api-token].
- When prompted for Credentials, choose Anonymous
- 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
- Hit the Expand button (double arrow) in the header of Column1. Choose whatever fields you want returned.
- 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.