Microsoft Excel is one of the most common data sources for Power BI. We can store Excel files in various storage types. The way we get data from Excel varies depending on the storage type. In this post, I quickly show two methods to connect to an Excel file stored in SharePoint Online.
Method 1: Getting the Excel File Path from the Excel Desktop App
This method requires you to have the Excel application installed on your machine. In this method, we open the Excel files stored in SharePoint Online in the Excel Desktop App in our machine and get the file path from there.
In SharePoint Online go to the desired document library then follow these steps to make it work:
- Select the Excel file
- Click the Open button
- Click Open in app
This opens the Excel file in the Excel Desktop application. In the Excel follow these steps:
- Click the File menu
- Click Info
- Click the Copy path button
So far we got the Excel file path. The step is to get data from the copied path in Power BI Desktop.
Open Power BI Desktop and follow these steps:
- Click Get data
- Click Web
- Paste the path we copied from Excel in the URL text box
- Delete the
?web=1from the end of the copied path - Click OK
- From the Access Web Content page, click Organizational account
- Click Sign in and pass your credentials
- Click Connect
Important Note If you miss step 10, you will get the following error: Details: "The input URL is invalid. Please provide a URL to the file path on SharePoint up to the file name only (with no query or fragment part)."
There you have it. Now you can select tables for sheets and start building your reports in Power BI Desktop.
Method 2: Getting the Excel File Path Directly from SharePoint (without opening the file in the Excel desktop app)
Last week I had a session with one of my customers going through the very same scenario. The customer had to teach his other colleagues how to use Power BI to get data from Excel files stored in SharePoint Online. He thought, going through all the steps explained in the first method is too much, and it might not be easy for his colleagues to absorb it. So we thought to look at our options in SharePoint Online, that he found something interesting. So this method is originally discovered by my customer, Mr Callum Fraser. Follow the steps below to get the file path directly from SharePoint Online:
- Select the Excel file
- Click the ellipsis button
- Click Details
- In the Details pane, scroll down in the pane to find the Path section
- Click the Copy Direct Link button
In Power BI Desktop, just click Get Data, then select Web and paste the link in the URL text box.
The benefits of this method over the previous method:
- It requires fewer clicks to get the direct link
- The direct link does not have any query parts so we do not require to modify the link
- The whole process is easier to remember
As always, I would like to know your opinion. Have you used these methods before? Do you know a better way to get the data from Excel files stored in SharePoint Online? Please share your thoughts in the comment section below.
will this still work with refresh in powerbi.com?
Welcome to BIInsight.com.
Yes it does, these methods are only to get the data from Excel files stored in SharePoint Online.
They do not affect your capability to setup scheduled data refreshes.
Cheers
I tried this method and it didn’t work on the refresh because it relies on my credential to refresh the data that also involves MFA.
Hi Douglas,
Thanks for getting in touch.
The methods explained here are to get data from files stored on SharePoint Online which will not affect the data refresh.
If you used your credentials when connecting to the SharePoint, then the data refresh uses your credentials. You can always change the credentials from the Dataset settings on Power BI service to switch to a different user account.
I hope that helps.
Cheers
We should develop BI tools can be accessed and used as easily as excel sheets.
HI Soheil,
What happens if i move the file into another folder. or I rename the any folder in the path. Will the link break?
is there a way around that?
i know Google sheets, has this idea of a doc id, so no matter where you move the doc, its always linked through that ID, does Microsoft have something similar?
Hi there,
Welcome to BIInsight.com.
That’s a very good question.
You made me think of writing a blog post about it indeed.
SharePoint Online also has file ID, but it is used internally in SharePoint and I am not aware of a way that you can connect to a file (in your case an Excel file) from Power BI using their ID.
However, there is another way that can potentially help you with your challenge.
Instead of using the Web connector in Power BI, you can use the SharePoint Folder connector.
Then you need to paste your SharePoint Site URL in the connector. This way you will see all files (including Excel, Word etc…) stored on that site. The next step is to filter the results based on your Excel file name and expand the Excel (binary).
This way, you connect to the file regardless of the folder it is stored in.
But keep in mind, if you move the file to another SharePoint Site, then your report breaks.
I hope that helps.
Cheers
That helps, thank you!!
i was hitting brick walls trying to just Get Data from the input excel file in a Sharepoint (Document Sub) Folder…
thank you Soheil for the useful tips to workaround the Root restrictions (also unhelpful as it lists EVERYTHING).
a follow-up question – can i refresh the powerbi just by replacing the aforementioned excel file? (same filename in same location, since the Load & Transform has already been defined); thanks again Soheil!
Hi Gary,
Thanks for your feedback.
Regarding your question, as always, it depends on your specific scenario.
Replacing the Excel file with a new one (with the same file structure) does not automatically refresh your Power BI dataset.
But if you scheduled an automatic refresh on the dataset, then the data will be read from the new Excel file on the next scheduled refresh.
I hope that helps.
Cheers.
How to link Power BI to a EXCEL containing proficy Historian data in it.
Also, In excel, I am fetching continuous current data from a server using proficy Historian. This data is to be reflact in POWER BI to amke the dashboard.
Hi Akash,
Thanks for your question. Unfortunately, I am not familiar with Proficy Historian.
But if you’re storing the data in Excel and then storing the Excel files on SharePoint Online, then the source system that created the Excel file would not matter anyway.
I do not understand the second part of your question re. “fetching continuous current data”.
Can you please elaborate?
Cheers
Hi Soheil,
Thanks for the quick reply & suggestion.
I’ve mapped a server (Exaquantum or PIMS) in Excel using Historian and getting all the live parameter of that server to my excel. In Excel, all the data which I got from that server will update automatically.
Now I want that, these data to be link with Power BI and also update automatically as per Excel file.
I tried the SharePoint way as suggested by you and many others, but here I got an issue.
While connecting the SharePoint to BI, after the Access web content window, I’m getting error of
“We couldn’t Authenticate with the credentials provided”
Also when I tried to tap the “Sign in”, the microsoft window open for a millisecond and disappeared.
Please help me with this…
Hi Akash,
Honestly, your use case is still a bit unclear to me which can be my lack of knowledge of Historian.
About the disappearance of the login window in milliseconds, I haven’t faced this issue in Power BI Desktop.
Unfortunately, I do not have a remedy for that.
If I were you I would take the following steps that may or may not help:
By doing the latter you ensure the credentials are not cached.
Hopefully, these steps can help.
it works fine on PBi desktop but when i upload it to pbi cloud service it stops refreshing. kindly suggest
Hi Vijay.
Just log into Power BI Service, navigate to the dataset settings and enter you SharePoint credentials.
Cheers
When I get to the authentication screen, organizational account is not an option. I only have anonymous, windows or microsoft. when I try to connect to other data sources, I see organizational as an option.
Hi John,
Are you using SharePoint Online or SharePoint On-prem?
If using the on-prem then you require On-premises Data Gateway installed on your local network and the SharePoint data source configured on Power BI service.
You can find some resources about how to plan and work with the gateway here:
http://biinsight.com/tag/on-premises-data-gateway/
Hopefully that helps.
Cheers
Superb example with screenshots – the way a tutorial should be done. Kudos
Hi Soheil
What would happen if I share the report to someone from my organization? Will the connection to the sharepoint folder still work? And will the report always have the current data from the excel file in the sharepoint folder?
BR
Hi Raj,
I suppose you will publish the report to Fabric (aka Power BI Service); in that case, the credentials you use on the dataset will be used to connect to SharePoint and not the credentials of the users who open the report.
To keep the data up-to-date, you need to set the automatic data refresh of the dataset.
Learn more about Data Refresh in Power BI here.
Cheers.