Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
andywil456
Advocate II
Advocate II

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
v-yuezhe-msft
Employee
Employee

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

17 REPLIES 17
garythomannCoGC
Impactful Individual
Impactful Individual

Loading Excel Files from Sharepoint 

The 'double down arrows' icon within the 'binary' column was the vital step for me.  Not very intuative.

Yggdrasill
Responsive Resident
Responsive Resident

This works for me and it's a baffle why this aint documented clearly

 

  1. Create a blank query
  2. Paste this code and replace the obvious string with your own
    let
            Source = SharePoint.Files("https://COMPANYNAME.sharepoint.com/sites/SITENAME/", [ApiVersion = 15]),
    in
            Source
  3. You might get an authentication prompt. Log in using OAUTH.
  4. Find your file and click on "Binary" which is the first row of the table that pops up

 

edit: Some firms have their own "local" url so instead of COMPANYNAME.sharepoint.com the string might just be something like COMPANYNAME/sites...
To find this url you can go on your sharepoint site and it's simply written in the URL window in your browser

Alwyn1991
Frequent Visitor

you may well be aware of the soultion now,

 

if you use Get Data and use sharepoint folder use the sharepoint site name and when in transform data navigate to the file you want this also helps when uploading the report the credentials login for scheduled refresh also

Anonymous
Not applicable

The YT film explains the proces, with a better way to get the right url:

 

https://www.youtube.com/watch?v=gJF2e_43FRY

 

 

Belgy23
Regular Visitor

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.

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!

 

 

Agree!

This process needs to be well documented. I often need to share an Excel file wich serves as my database to an organizational sharepoint so others can use it and update the file. Everytime I do it I encounter so many challanges that are not related to org restrictions but mainly to the way Power BI works with sharepoint...

v-yuezhe-msft
Employee
Employee

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.

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. 

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

 

 

 

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.

Anonymous
Not applicable

I am having a credentianls issue as well!

 

Power Bi "(404) not found

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.

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"

This actually works really well!

 

Thank you so much.. 🙂

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

This was exactly what I was looking for! Thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.