Data Public Necessary for Query Links? [NO, it is not]

User Support Data Management
,
1

Hello,

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?

Thank you!

2

Greetings @BPlayfair123 and welcome to the community.

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 all

3 Likes
3

@nat, which version of the Excel do you use to Power Query?

4

Hi Nat,

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?

Thanks!

2 Likes
5

@BPlayfair123 I just logged a suggestion - please feel free to comment/vote so it can be considered by the KoBo team! All the best.

2 Likes
6

@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 article and 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!

I use Excel O365 but it’s available in pretty much all versions of Excel I believe - there by default since Excel 2016, or a downloadable add-in for earlier versions: Excel | Microsoft Power Query

2 Likes
7

@nat, I checked that with Office 365 and could see it there. But till recent I was using Office 2019 and did not see the same there.

We will update the article soon. Thank you for bringing this to the community.

1 Like
8

@nat, I tried this on Office 365 (Excel) with this version (which is the latest):

image

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.

9

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

Thanks

2 Likes
10

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.

2 Likes
11

@nat, please be informed that we have updated the support article. FYR:

Thank you for providing suggestions for the same. Expecting the same in the upcoming days too.

1 Like
12

thanks @nat for your on point and constructive input! This was the first I could find (still!) about not needing to make data public which in our case as well would be extremely problematic.
Do you happen to know if it would be possible to do the same using API keys instead? (I have tried and so far 404).

thanks again!

13

Hi @halfdan,

It is possible to get your data to Power BI or Power query with some basic authentication or token, Here you a tutorial if you need: Islam Burcu on LinkedIn: As a person using this method frequently and saving time, I would like you…

1 Like