Forum Discussion

webportal's avatar
webportal
Icon for Impactful Individual rankImpactful Individual
6 years ago
Solved

Connect data flow to Excel file stored in OneDrive

This should be simple: I need to create a data flow to get data from an Excel file stored in my OneDrive.   The URL is: https://xxx-my.sharepoint.com/personal/xxx_com/Documents/PowerBI/P&L%20CDS%2...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi webportal ,

     

    Usually, this encompasses to extra characters in the URL that are part of the link that OneDrive generates for the file, but not accepted by PBI (something like :x:/r/ after site address and  ?d=w3a41a... after the file name).

     

    The URL in your post, however, looks valid. In my test, I had this error when I entered an incorrectly formatted URL and signed it. When I fixed it the error did not go away, but I was able to sign in and proceed with Next. Maybe this will work for you too?

     

    An alternative way, which I found, was via Web API connector. You can try this too.

    If this does not help, this is the Source step generated by PBI, you can try altering it for your case and then provide credentials when requested by PBI:

    Source = Excel.Workbook(Web.Contents("https://xxx-my.sharepoint.com/personal/xxx/Documents/Book2.xlsx"), null, true),

    Kind regards,

    JB

ToddChitt's avatar
ToddChitt
Icon for Super User rankSuper User
6 years ago

I too am getting this same error and hoped this post would help. It did not. 

I removed the :z:/s from between the "sharepoint.com/" and the site name so the URL looks like this:

https://<my ompany>.sharepoint.com/<Site name>/<Document GUID>

 

I could not tell exactly the options in the original post, but it looks like "None" for the Gateway seleciton, and "Organizational account" for the Authentication. Below that is says that I am already signed in. And this is the same Organizational Account I use the access the document.

Yet I still get "Invalid credentials" error.

 

Frustrated. This should be a no-brainer for Microsoft! At LEAST get me good error text code. "Invalid credentials" if the URL is bad?

 

"Sir, our automotice scan tool Error Code says that your engine is misfiring on one of the cylinders, so we're going to need to replace all four tires."

 

Sorry for the rant. 

Any suggestions?

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi ToddChitt,

 

I had this issue, but not very often, so I do not remember how exactly I solved it.

My usual steps when something goes wrong at the "credential/sign-in" stage are:

  • check that you definitely have access to the file you are trying to get data from - open it in the browser;
  • close off all apps and restart (I hate it when offered to do by IT support, but sometimes it helps);
  • connect to another storage type (Sharepoint if using OneDrive or another Sharepoint site/domain);
  • clear Data source setting in Power BI relating to this location (and any upstream locations !!! as they may override the settings).

Hopefully, something from the above will help.

 

Kind regards,

JB