Hello everyone and welcome to my blog. In today’s blog I will show you on how you can connect to an excel file stored in SharePoint document library from Power BI Desktop.
If you look this requirement, it seems really simple. Connecting to an excel file is not something we usually consider as a challenge. Yes it’s easy but it’s not straightforward.
Let’s begin our journey. I have the Device-Order-Data.xlsx file stored inside ‘Shared Documents’ SharePoint document library. And I want to connect to this excel file from Power BI Desktop. I open my Power BI Desktop and explore the options we have.
The first and the most obvious options is obviously to use the SharePoint connector from Power BI Desktop. Below are the options you get when you search for SharePoint.
As you can see, none of these connectors suits our purpose. We have connectors to show list data and the contents inside a folder but not really to connect with the excel file stored in SharePoint document library.
To connect to an excel file stored in SharePoint document library, you will need to use the Web option.
When you choose the Web option, it asks for a URL. And this is where the trick is.
Whenever you see the URL, the first option that makers usually think is to use the Copy Link option.
Let’s try it. I take the URL out of Copy link option and put it in the URL section. And when I try to login using Organizational account, you will receive the following error. Even after entering the credentials it won’t connect.
So what’s the other option? Select your file and then navigate to file details.
Navigate all the way down and you have a property called Path.
Copy the Path link and use the same path for the URL field in the Web connector in Power BI Desktop.
This time when you connect using Organizational Account, you will be able to connect to the excel file.
Quite easy right? But trust me, if you are not aware of this you might be scratching your head as to why this is not working before you finally find the solution.
Hope you like this post. If this post has helped you, you can buy me a coffee. Link on the right side panel.
You will also like the below posts.
Debajit Dutta
Business Solutions MVP