cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
vtcusascott
Frequent Visitor

Share Point/Office 365 connectivity issues

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.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Share Point/Office 365 connectivity issues

@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
1.PNG

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

vtcusascott
Frequent Visitor

Re: Share Point/Office 365 connectivity issues

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!

View solution in original post

5 REPLIES 5
Moderator v-yuezhe-msft
Moderator

Re: Share Point/Office 365 connectivity issues

@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
1.PNG

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

vtcusascott
Frequent Visitor

Re: Share Point/Office 365 connectivity issues

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!

View solution in original post

ravi1459
New Member

Re: Share Point/Office 365 connectivity issues

Nice article on sharepoint please keep sharing
ravi1459
New Member

Re: Share Point/Office 365 connectivity issues

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

risoli
New Member

Re: Share Point/Office 365 connectivity issues

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

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors