Kobo to Power BI - securely via API

mikehoneymf · December 3, 2019, 5:19am

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.

Kal_Lam · December 3, 2019, 5:30am

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!

mikehoneymf · December 3, 2019, 7:17am

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

Mike

Quentin · December 10, 2019, 9:24am

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

DavidT · December 11, 2019, 3:23am

I managed to get the data into Power BI so I was initially very happy about that. However, I have a provlem with the JSON file that is extracted from the Kobo database… FYI, I am doing a household survey, and was only able to extract one record for each of the 126 households, but, when I expanded the field that said group, which contianed data about the children, I only got one record per household. This could be my inexpereince using JSON files on Power BI or is it an issue with the extract.?

stephanealoo · December 18, 2019, 5:04pm

Hi David,
Your data is there, however you need to just expand your data appropriately. Just to ensure that you are getting a wider support, have a look at the forum here to see if any users has cracked this.

Stephane

jblackman · January 30, 2020, 7:03pm

I hope it is ok to add a question to this posting.

In the Help Center article, “Pulling your data into Excel Power Query”, it states

Before transitioning over the Excel, make sure that there is a) at least 1 record stored in your data table in KoboToolbox and b) under the Project’s settings, set it to “share data publicly”.

If we use the UNHCR INFORMATION MANGEMENT KoBo Toolbox methodology of connecting KoBo data to Power PI (https://im.unhcr.org/kobosupport/) listed above, do we need to make the data available publicly?

DavidT · January 31, 2020, 3:30am

Came back to the project 5 weeks later and was able to do it. Thx

DavidT · February 1, 2020, 2:24am

Mike,

Thank for for writing the article - very useful. However, could you please modify your instructions a little

When Kobo generated the token it come out as

{“token”: “Exampleabcedxxxx999”}

However, after a lot of testing I found in Power BI the format it had to be entered in was

Token Exampleabcedxxxx999

DavidT · May 27, 2020, 5:45am

Hi mikehoneymf,

I am attempting to set up a scheduled refresh with Power BI online.
Note:

I have a Power BI pro licence.
I can refresh kobotoolbox in my Power Bi Desktop software
I was able to refresh from Power Bi serivces back in Feb using your very helpful instructions but i am struggling now.
Could you please you can tell me what settings you put in the data source credentials?
Was the Privacy level setting for this data source None or Private.
I click the skip test conection and it dones not work

At present, I have added “&refresh=1” to the end of the URL that i got to share on a public website as I want to refresh every 1 minute just to test this out. Nothing seems to change however.

Does your solultion still work?

mikehoneymf · May 27, 2020, 7:56am

Sure, no changes. I set:
Authentication method = Anonymous
Privacy level … = none
Skip test = checked

I’m don’t think that refresh parameter will work the way you imagine - it’s a pull into Power BI, not a push from kobo.

Refresh limits & features in the web service are determined by your Power BI license level.

janna · June 8, 2020, 10:34am

Hi @mikehoneymf,
I’m interested in doing this, however I’m not sure how to get the API token. When I go into REST Services, it requires an “endpoint url” - do you know what I should enter there if I just want to import into PowerBI?

Thanks
Janna

mikehoneymf · June 8, 2020, 2:12pm

Hmmm looks like that has changed recently.

Can anyone more familiar with the new REST Services advise on this?

janna · June 8, 2020, 8:05pm

Thanks @mikehoneymf for your help. @Kal_Lam, any ideas?

Kal_Lam · June 9, 2020, 6:12am

Hi @janna,

At the moment kindly please follow the support article Using the API to get the API token. In the next release of the KoBoToolbox, you would get the API token directly from under your ACCOUNT SETTINGS without having to follow all the steps as outlined in the support article.

Have a great day!

stephanealoo · June 9, 2020, 8:54pm

HI @janna
Following the trail on the other side, I believe the issue has since been resolved.

Stephane

janna · June 9, 2020, 10:32pm

Hi @mikehoneymf,
Thanks to @Kal_Lam’s pointing me in the right direction to get the API token, I was able to follow the steps you shared and get PowerBI connected up to project data where I hadn’t made the data public - woohoo!

However - I basically have got the JSON data in the PowerQuery showing as a LIST of RECORDS. And if I just say “to Table” in the PowerQuery, it gives me an error:

Expression.Error: We cannot convert a value of type Record to type Text.
Details:
Value=[Record]
Type=[Type]

I’m not sure if you had this same error, as I’m pretty sure it’s based on the fact that there are a few columns in the KoboToolbox data that come in as “LISTS” or “RECORDS” - and my feeling is that Power BI isn’t converting them, or doesn’t know quite how to.

Do either you or @iker.montes have any advice on what to do once the JSON data is in the Power Query, but how to then convert the records to a table?

thank you so much for your help!
Janna

iker.montes · June 10, 2020, 7:59am

Sorry @janna, but I must confess that JSON is a prohibited territory for my…never worked with those files.

janna · June 10, 2020, 8:15am

Thanks @iker.montes - I’m the same, my knowledge of JSON is very limited right now.

I was wondering if instead of JSON there was a link that could pull in csv format, like you did in your PowerBI connector for shared data.

@mikehoneymf or @Kal_Lam:
In the above examples made by @mikehoneymf, you said to use this url for the API connection into Power Bi: https://[kpi-url]/assets/[form-uid]/submissions/?format=json

This worked for me, but now I’m getting stuck actually turning the JSON data into a usable table. I think csv format would work better for me. Is there a url that would link csv data instead?

(I tried just replacing /?format=json with /?format=csv - but that didn’t work for me)

Thanks!
Janna

iker.montes · June 10, 2020, 11:12am

Just out of curiosity I tried this method and gives me good results.
The query in the advance editor was:

let
Query1 = let
Source = Json.Document(Web.Contents(“https://kobo.humanitarianresponse.info/assets/FORM_ID/submissions/?format=json”, [Headers=[Authorization=“TOKEN”]])),
issues = Source[issues]
in
Source,
#“Converted to Table” = Table.FromList(Query1, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#“Converted to Table”

Got my data ok!

No problem with the JSON conversion to table. Just 2 clicks.
Althouh it would be great to have the tada as labels, So It can read it without further manipulations.
If you, @janna, want send me teh credentians in a private post and I can have a look!
Regards