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.?
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
1 Like
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?
Came back to the project 5 weeks later and was able to do it. Thx
1 Like
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
1 Like
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?
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.
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
Hmmm looks like that has changed recently.
Can anyone more familiar with the new REST Services advise on this?
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 
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…
If you look in the forum, the most vvisited topics are related in general on how to connect Kobo to other tools.
