Exporting data errors (with Excel and Power BI)

[Mahmoud Abo Shabo]

Hi Dears,

I set up an export data "Export Forms" to "Web Page (Excel Dashboards)" with "Create a Daily Saved Export " checked.
Then, I get the link to and put it as a web link in Excel sheet asked me to put a username and password; I put my CommCare authority but it did not work and give me attached error, I have country admin authority in CommCareHQ.

With MS Power BI, I tried both (Basic and Web API) also did not work (with API error given "The credentials provided are invalid"). 

Could you please, advise me how to fix that or any other way to get the data..
Thanks

[Sheel Shah]

Hi Mahmoud,

It looks like Power BI/ Excel Power Query doesn't properly handle basic authentication.  See this article:

https://social.technet.microsoft.com/Forums/en-US/3d81cbcf-2510-41bb-b325-693d986d6641/power-query-vs-basic-authentication-webserver-with-ssl?forum=powerquery

You can get around this by adding your own custom power query expression.  

- In Excel, go to New Query, but choose Other, Blank Query

- In the query editor, then click on View, Advanced Editor

- Then paste in the following:

let

    Source = Web.Page(Binary.Buffer(Web.Contents("<URL To Saved Export>")))

in

    Source

Note that the URL needs to end with a slash. Excel will prompt you to enter your username / password via Basic Auth then.  You can then use the query editor to choose the data you want.

Thanks,

Sheel

--
You received this message because you are subscribed to the Google Groups "commcare-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to commcare-user...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Sheel Shah

Project Manager | Dimagi

m: +1.781.428.5419 | skype: sheel_shah

[Mahmoud Abo Shabo]

Hi Sheel,

Thanks for your support! It works now.

Best Regards

[Eric Stephan]

Hi you guys --

I am reading through these PowerBI threads with interest.

Can you explain one thing to me?

From these examples, it looks to me like you are making a flow that goes like this:

CommCare in Cloud   -->  PowerQuery operating on a desktop computer  -->  PowerBI (to present a cloud dashboard.)

• Am I correct in describing that that way?

• Second, what if I wanted to make a dashboard that would take the data straight from CommCare in the cloud directly to PowerBI, where it would automatically update a cloud-based cloud dashboard, without any human taking steps at their computer?  For example, would ideally update this automatically and it would always be working and updating, two months later, without a person needing to manually do anything.

Any thoughts?

Huge thanks

Eric

[Eric Stephan]

Just to bump this question about PowerBI... is it possible to make a flow that goes straight from CommCare to PowerBI?  that would work "live 24/7" and not have to have a human manually massage data on the way in to the dashboard?

Eric

[Mahmoud Abo Shabo]

Dear Eric,
I am sorry to do not reply to your previous text. Yes exactly that's correct.
I am working on Power BI desktop to get the data from CommCare >> Power Query >> Do the analysis >> present a dashboard at cloud.
For data update, CommCare give you an option to update the data after one day or you can update the data at any time you would. If you schedule Power BI to refresh the data time to time it seems that the data will update with the dashboard automatically without human interaction. Till now I did not schedul the process but I refresh the data manually and it gives me all collected data and that directly reflected on the dashboard automatically.

I hope that good and if you get other results pleas update me. I am happy to share with you my latest update once I completed my project.

Regards
Mahmoud

> --
> You received this message because you are subscribed to a topic in the Google Groups "commcare-users" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/topic/commcare-users/Wbt-TNsFl2c/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to commcare-user...@googlegroups.com.