Peculiar behavior of Power Query (M) access to SharePoint folder/file RRS feed

  • Question

  • I have an M query to get data from an excel file in a SharePoint document library:

        Source = Excel.Workbook(Web.Contents("https://***"), null, true),

    Which works just fine, but...

      Source = Web.Contents("https://***"), 

    ...results in an error 'We couldn't authenticate the credentials provided'

    Why would the credentials work for Web.Contents as nested function and not on its own?

    I have also tried.... 

     Source = SharePoint.Files("https://****", [ApiVersion = 15])

     Source = SharePoint.Contents("https://****")

    ...which causes the error:

        DataSource.Error: SharePoint: Request failed: The remote server returned an error: (400) Bad Request. (Bad Request)
        Url= Documents/Operations/_vti_bin/ListData.svc

    I'm using organisational credentials and changing the data source credentials type and privacy levels hasn't worked at all. 

    Any thoughts on how to overcome this? I just need to expose sharepoint files to excel power query for myself and others in my organisation.

    Many thanks,

    Thursday, June 28, 2018 9:00 PM


  • Hey,

    Instead of using:


    try using the root level of your sharepoint site instead like this:


    • Marked as answer by Joe Hobbs Friday, June 29, 2018 3:49 PM
    Friday, June 29, 2018 2:47 AM