Error when trying to extract from Sharepoint Document Library

[Anonymous]

Hi all,

Am attempting to use Power Query to pull Columns from 100+ Excel worksheets stored in a Sharepoint Document Library . I am able to do this fine when the Excel sheets are stored locally but once I try and pull it from Sharepoint I run into issues.

Method I am using: 

Step 1: Get Data from Sharepoint Folder 

Step 2: Edit in advanced query. Change File to Contents (gives me content of entire sharepoint site)

    let
          Source = SharePoint.FileContents("https://team.sharepoint.com/teams/location", [ApiVersion = 15])
    in
          Source

Step 3: Select the table link to see all the Workbooks contained in the document library.

Step 3: Create a custom column with the following code 

    Excel.Workbook(File.Contents([Folder Path]&[Name]))

When I try this method, I get the following error.

DataFormat.Error: The supplied file path must be a valid absolute path.
Details: "URL to excel file"

Any one have any solutions?

Thanks in advanced.

[v-jiascu-msft]

Hi @Anonymous,

It's better to do it using the built-in functions than modifying the query code directly unless we have to. Please refer to this guide to import Excel workbook from Sharepoint. If you just need one workbook, you also can refer to Import-Data-from-an-Excel-sheet-in-SharePoint.

Best Regards,

Dale