Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
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:
Thanks again for your help I appreciate it!
Nice article <a href=http://www.itorigins.com/sitecoreonlinetraining.aspx>sharepoint</a> please keep sharing
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
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
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:
Thanks again for your help I appreciate it!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
51 | |
19 | |
12 | |
11 |