cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

Import Data from an Excel sheet in SharePoint

I'm trying to get data from an Excel spreadsheet stored on SharePoint.  

I have tried doing this by the URL path but it cannot find at file level. The only way that seems to work, is  to download the files into my documents and then uplaod into Power BI. As i will be doing this on a weekly basis this is not ideal.

 

Thanks 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Import Data from an Excel sheet in SharePoint

Hi @andywil456,

You can import data from Excel stored in SharePoint to Power BI Desktop by using “Get Data>Web” option as follows.
Capture.JPG

Capture.JPG

Based on my test, it works as expected when I enter the URL of Excel file via the option, data is imported successfully to Power BI Desktop.


Thanks,
Lydia Zhang

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

16 REPLIES 16
Highlighted

Re: Import Data from an Excel sheet in SharePoint

I wrote this a couple of years ago, but I assume it is still relevant and works with Power BI 

http://exceleratorbi.com.au/consolidate-multiple-excel-files-in-sharepoint-using-powerquery/

 

I thought there was also a new SharePoint connector too.



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Highlighted
Microsoft
Microsoft

Re: Import Data from an Excel sheet in SharePoint

Hi @andywil456,

You can import data from Excel stored in SharePoint to Power BI Desktop by using “Get Data>Web” option as follows.
Capture.JPG

Capture.JPG

Based on my test, it works as expected when I enter the URL of Excel file via the option, data is imported successfully to Power BI Desktop.


Thanks,
Lydia Zhang

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

Highlighted
Helper II
Helper II

Re: Import Data from an Excel sheet in SharePoint

Hi @v-yuezhe-msft

 

May I know where exactly did you get the URL from?  I have created a web page which contains an excel file and I am using the Web data source just like the way you are dping but I don't know which URL to put and where to get it from. 

Highlighted
Advocate I
Advocate I

Re: Import Data from an Excel sheet in SharePoint

Go on your sharepoint
Open the excel file with excel (desktop, not online)
It'll show a pop up with the path (but you won't be able to copy and paste)
So, once Excel is opened:
Click on File
INFO
(bottom right corner) right click on OpenFileLocation
Copy link
then paste it in powerbi AND

add "/nameOftheFile.xlsx"

Highlighted
Advocate I
Advocate I

Re: Import Data from an Excel sheet in SharePoint

Go on your sharepoint
Open the excel file with excel (desktop, not online)
It'll show a pop up with the path (but you won't be able to copy and paste)
So, once Excel is opened:
Click on File
INFO
(bottom right corner) right click on OpenFileLocation
Copy link
then paste it in powerbi AND

add "/nameOftheFile.xlsx"

 

Capture.PNG

 

 

 

Highlighted
Advocate I
Advocate I

Re: Import Data from an Excel sheet in SharePoint

This worked, thanks.  But now I'm trying to publish to web, I'm getting a failure of credentials.  Any ideas how to get that working?  Many thanks, D.

Highlighted
Frequent Visitor

Re: Import Data from an Excel sheet in SharePoint

I needed to do one more step.

Remove "Forms/AllItems.aspx​"

Then I appended my file name.

It worked for me after doing above steps along with the steps mentioned in your post.

Highlighted
Regular Visitor

Re: Import Data from an Excel sheet in SharePoint

I found this method the easier when pulling tables off a XLS into powerbi if i'm not manipulating data from multiple XLS files from a sharepoint folder.

 

It's easier to use this Web method then Sharepoint Folder option within PowerBi Desktop (by FAR) which is very cumbersome.

Highlighted
Advocate I
Advocate I

Re: Import Data from an Excel sheet in SharePoint

This entire post thread needs to be consolidated and documented in the official Power BI documentation site.

 

It is SO HARD to work this out from within Power BI Desktop and I only do it a few times a year and I STILL forget the process at times!

 

 

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors