Share Point/Office 365 connectivity issues

[vtcusascott]

I'm having a huge amount of difficulty with two issues related to my company's housing of all its work related Excel files in an Office 365/SharePoint environment.

Though the desktop tool works great on local files and in tandem with SQL generated versions of my ERP, I'm stuck when trying to connect to Excel files that live in our SharePoint  system. I've tried "getting data" from a "Web" source, a "SharePoint Folder" and a "SharePoint List" with little luck.  When I am able to bring information in it looks to be page data, is unusable, or does not correlate with what I would expect to see with the supplied URL. If I try to supply a more detailed URL it fails.

As a connected issue can map a network location to the SharePoint location on my local drive and tell PBI to get an excel file using that path, but ultimately that path breaks when I restart my machine so It takes automated refreshing off the table. Incidentally connecting to these files with the online PBI service is easy but I can't build and do the same data manipulation on the web so that shuts me down there.

Any help anyone can provide would help. Additionally, if anyone could recommend a good consultant that has deep expertise on SharePoint/PBI I'm really thinking that they could solve this issue pretty quickly for us, so suggestions would be welcome.

[v-yuezhe-msft · Accepted solution]

@vtcusascott,

If you store Excel file under SharePoint Online Documents, you can paste the URL in Web entry of Power BI Desktop and use “Organizational account” to connect to it.

https://microsoft.sharepoint.com/XXX/XXXX/Shared%20Documents/ExcelName.xlsx

If you store Excel file in SharePoint Online folder, enter the root URL of SharePoint online site in “Get Data->SharePoint folder” , then use “Microsoft account” to connect to it, you will be able to extract data from your Excel file.

If you still fail to connect to the Excel file, please post the error message here.

Regards,
Lydia

[ravi1459]

Nice article <a href=http://www.itorigins.com/sitecoreonlinetraining.aspx>sharepoint</a> please keep sharing

[v-yuezhe-msft · Accepted solution]

@vtcusascott,

If you store Excel file under SharePoint Online Documents, you can paste the URL in Web entry of Power BI Desktop and use “Organizational account” to connect to it.

https://microsoft.sharepoint.com/XXX/XXXX/Shared%20Documents/ExcelName.xlsx

If you store Excel file in SharePoint Online folder, enter the root URL of SharePoint online site in “Get Data->SharePoint folder” , then use “Microsoft account” to connect to it, you will be able to extract data from your Excel file.

If you still fail to connect to the Excel file, please post the error message here.

Regards,
Lydia

[risoli]

Hello

I am not able to link any excel report from SHAREPOINT wrong link message from PBI asking for a clean link but I am not able to get it from share point folder.

Please help

regards

Pierluigi

[vtcusascott · Accepted solution]

Thank you very much for your response. I'd forwarded it on to our Share Point consultant and it was helpful to him in making the following suggestion- which worked perfectly! Keep in mind that the structure of our document library was different than it might appear if you just create a page or team site. In any case I hope this might be useful to someone else having the same problem as it was a roadblock for me until I was given this suggestion. The hyperlink mentioned worked well when used in tandem with the "Web" data sorce in Power BI. Here is the relevant part of his response......

"So for the views used throughout your company, the file menu using the “3 dots” to the right of the file name provides the most file options which is why  it was chosen… except the one option to get the cleanest URL  😉

To get the cleanest URL for a file:

1. Click the checkbox to the left of the file name.  This will expose a “Files” and “Library” menu options in the top, light blue bar of SharePoint.
2. Click on “Files” in the light blue bar of SharePoint.
3. Click on “View Properties”
4. This will produce a light “Properties” view, but the most important is the hyperlink to the file.  Right click on the hyperlink to the file and choose “copy link address” (text for copying link may vary depending on which browser you are using… I am using Chrome)

Thanks again for your help I appreciate it!

[ravi1459]

Nice article on sharepoint please keep sharing