I am trying to find a way to connect a Kobo data source to either PowerBI or Excel. However everything I’ve seen on this topic indicates that the user must publicly share their data. The data I work contains sensitive PII and therefore cannot be made public in any way.
Is there any work around to this so that I can connect Kobo to Excel or PowerBI without making the data public. Or are there any plans to update/improve Kobo in this way so that when a user collects sensitive data we can still generate reports without the data being public?
I do a lot of PBI + PowerQuery/Excel integration and can assure you that you do not need to make the data public in order to connect to PowerBI or PowerQuery/Excel. You can keep it private and use Basic Authentication (i.e. KoBo username and password) to connect.
Agree that the documentation needs to be updated (as the info on the support site is currently wrong as per the below). @Kal_Lam may you or someone else in the KoBo team please update the doco?
Thanks a ton! This worked fine when I used the basic authentication.
In PBI using this (.xls) pulls the data so that the column headers are quite long. I think maybe the name of this is combined column. But in order words it might read: consent/hhsize/gender/… etc for many columns so that it is hard to read in PBI.
Were you able to change the API link in anyway to have a more readable format for the fields in PBI?
@Kal_Lam I also notice that the support.kobotoolbox.org documentation still refers to the old v1 API in several places - and as far as I understand from this support articleand this post, the v1 (KoBoCAT) API is supposed to be being deprecated in favour of the new KPI API. Probably worth doing a full overhaul of these documentation pages to be sure they’re pointing people in the right direction!
@nat, I tried this on Office 365 (Excel) with this version (which is the latest):
Note: You could check this under File>Account.
And I didn’t find the basic authentication. Maybe you are below the latest version. If you still wish to continue the same, my advise would be that you do not update your Office 365 as the latter version seems to have dropped this feature:
However, if you have multiple user accounts of Office 365, you could still try updating the same to see if the latest version should support at your end.
Hi @Kal_Lam the link you have given above is not for PowerQuery, or even Excel - that link relates to Microsoft Exchange (i.e. email products).
To see the auth settings you first set up a query with a connection to an external data source (in this case web) - then it will ask you for the authentication settings the first time it tries to connect. Alternatively, To see the auth settings for an existing data source then when you’re in the PowerQuery editor you go to Data Source settings / Edit Permissions. This is the relevant help info: Authentication with a data source | Microsoft Docs
For sure Microsoft still supports this in the latest version of Excel, and will continue to do so for the foreseeable.
Actually the UNHCR KoBo Support page has quite a good intro for KoBo integration specifically, with screenshots of the authentication: KoboSupport
Thank you @nat, I could finally configure the Office 365 with the Basic Authentication and here confirm that the latest version of the Office 365 also supports the same. Will update the support article on Excel Power Query accordingly. Will also add a description on how to configure the settings that enables Basic Authentication which should be very helpful for a lot of users.