none
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)
        Details:
        DataSourceKind=SharePoint
        DataSourcePath=https://lankelma.sharepoint.com/Shared%20Documents/Operations
        SPRequestGuid=a0a4759e-7097-6000-0797-034ff0b2c118
        Url=https://lankelma.sharepoint.com/Shared 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

Answers

  • Hey,

    Instead of using:

    =SharePoint.Contents("https://lankelma.sharepoint.com/Shared%20Documents/Operations......")

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

    =SharePoint.Contents("https://lankelma.sharepoint.com")

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