Kobo to Power BI - securely via API

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

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!

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

Stephane

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

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

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

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

1 Like

Hi @janna,
Once you have that you need to be able to expound this to get the data and define it. I confirm that this is just one of the steps. Can you send me a private message so that I temporarily have access to the PowerBi

Stephane

Hi @iker.montes, @mikehoneymf, @Kal_Lam,
Thanks so much for collaborating over the last couple of days! I’ve finally got it all working with all of your various help!
Now on to the next steps of connecting a few more forms and visualising :slight_smile:

Hope you’re all well,
Janna

1 Like

Thanks all for jumping in and helping on this while I was distracted. I’m glad to hear @janna got it working in the end.

Taking a step back, it seems this process is probably too hard and might be a blocker for a lot of Power BI (or other dataviz) efforts on Kobo, which would be a shame. Engaging DataViz seems so important, and Power BI seems the dominant tool now.

If there are any web devs reading this, a better architecture to encourage Power BI authors would be to build a Data Connector using this SDK:

With that in place, Kobo data would be (almost) as easy to consume in Power BI as an Excel table or database.

100% agree, that it is a little bit on the hard side to connect Kobo and power bi…:frowning:
If you look in the forum, the most vvisited topics are related in general on how to connect Kobo to other tools.

Hi @mikehoneymf
Thanks so much for providing this. I will look at this and see how to best advise other users on the incorporation of the same.

Stephane

Hi everyone!
Okay - Finally got around to creating a resource to help others connect KoboToolbox and Power BI using an API connection!

I’ve created a video tutorial here for anyone visiting this topic/thread and wanting a video tutorial to help do it step by step!

Click here to watch: Load KoboToolbox data to Power BI via API connection

Thanks!
Janna

6 Likes

Hi @janna,

Thank you for sharing this wonderful resource to the entire community! This is really very helpful and should benefit a large number of KoBoToolbox users.

Have a great day!

1 Like

and any luck on obtaining the “LABELS” in different languages in a private way. That would be super interesting as well.

2 Likes

That makes two of us. But i guess asking is how we becoming pro. Lol

Hey Janna. What a super helpful resource! I have subscribed to your Youtube channel, as I’ve now moved on from Medair to a new role as MEAL Advisor. Hope all is well with you!

2 Likes

This works… But only in Power BI desktop …
Its not getting updated for PBI services.

Pls help

Yes, i was able to connect PBI desktop. But it doesnt work with PBI services… Pls guide

Hi,

This is a question that NGOs seem to be asking about more and more frequently, usually in terms of PowerBI. I have never heard anyone find a way to get the labels directly from an API connection which would of course be the best option. However it does seem that you can pull the labels from the API separately to your data so it is in theory possible to connect the two.

This is however not to be underestimated as it means writing a script which can run in PowerBI and which can take each field in turn, join it to the table of labels, map the text and then delete the temporary join. Ideally it would run in a loop and be coded in such a way that the user would only need to indicate the relevant table names and it would apply to all of the fields contained. In this way the same script could run in any dashboard. It would be too much effort to code for just a one off application but should be possible to code in a generic way.

It would definitely slow down the refresh time so possibly but the idea would be to build the dashboard without the labels and then run the script once at the end to automatically swap the names for the labels.

Right now I wouldn’t have time to work on such a trial but if its something which is likely to remain a gap on the API side, then such a work-around is something I would be interested in looking into in the future.

Noel

5 Likes