Authentication using API key

Users
1

Hi,
Anyone here using PowerBI or power query in excel? I'm trying to integrate data from Commcare using web query, I used to do that using the advanced editor in the query, and copy the link from "Excel dashboard integration" in Commcare, It was working fine, but now Microsoft doesn't support the basic authentication, I generated an API key and was hoping to solve the authentication problem, but still it didn't work, even after I copied the key in its slot (the photo attached).
I Tried som ready M codes with the API name in the code but didn't work
Does anyone know how to solve this? where to put the API name and key?
any kind of help would be very much appreciated

image
image832×356 18.4 KB

best,
Aysha

2

Hi @aysha -

I just tested Excel Power Query using the excel dashboard feed from CommCare and using my CC credentials with Basic authentication and it works no problem.

Just to clarify you're using the get data > blank query and then going into the advanced editor and using the following:

let 
     Source = Web.Page(Binary.Buffer(Web.Contents("COMMCARE_DATA_FEED"))) 
in 
     Source

If that was working before for you have you tried clearing the permissions / credentials saved in excel? Get data > data source settings and then select the CommCare data URL and then select clear permissions.

Good luck.

3

Thanks for your replay, it seems I have a problem that's common between users (i searched the internet and many people have the same authentication issue) this's why I was using the advanced editor with the same code you have mentioned. used to work without a username and password, but now it's requesting credentials.
I'm lost :frowning:

4

hmm... I never knew you could query the data without credentials. Interesting.

Is there a reason you can't use your CommCare credentials for basic authentication? Are you trying to share the excel file to other users who don't have credentials?

5

excel is not accepting the credentials! I thought it's only me, but searching the internet, it seems many other people have the same issue, they are solving it by legacy wizard, but I don't have that either :face_with_raised_eyebrow: :roll_eyes:
that's why I'm searching for other ways to connect to power query

6

Can you share what happens when you select basic authentication? Is there an error?

Have you tried clearing permissions and starting again? Also, can you share what Excel version you're using?

7

yes I tried to clear permission, and I'm sure the credentials I use are the right ones, I attached the error
I'm lost!

error
error1281×395 22.8 KB

8

Hi @aysha,

Looks like I'm getting the same error now... My existing Odata connections are no longer accepting my CommCare credentials across different project spaces.

I'll keep this thread updated on the status.

9

Hi @aysha -

Well, not sure what the issue was but it has resolved itself. My gateway returned a bunch of refresh errors based on invalid credentials this morning and even had the same issue when I tried a power bi odata connection. However, just I tried it again (to make sure I'm not crazy) and it's working fine again... :man_shrugging:t2:

Have you tried connecting the excel dashboard feed to power query today? Maybe it'll work again...

10

I still have the same problem :frowning: good for you though. I think the issue with my data is related to excel with basic authentication, I thought someone might have an idea about API key which might solve the problem