Forum Discussion

andywil456's avatar
andywil456
Advocate II
10 years ago
Solved

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 th...
  • Anonymous's avatar
    Anonymous
    10 years ago

    Hi andywil456,

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




    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

Yggdrasill's avatar
Yggdrasill
Responsive Resident
6 years ago

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