Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

File on ShP site as a data source

Hello All, I have an issue with adding a file from ShP as a source. I tried with "Excel workbook" source and copying the hyperlink, but I keep getting errors. I tried few other options, but they see...
Need Help
BA_Pete's avatar
BA_Pete
Super User
3 years ago

Hi Anonymous ,

 

In Power Query, use the SharePoint Folder connector. The URL to enter will be something like this:

https://orgname.sharepoint.com/sites/sitename

 

You can get this URL easily enough by just navigating to the Home page of your SharePoint site and copying the URL from the browser.

It's best to start at this highest level as you then get a table with every file within the site that you can potentially import, and you also only have to store this high-level authentication on your gateway, making reusing the connection for other files in other folders much easier in future.

 

Pete

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thank you for your reply. I tried this method, but unfortunately, I got the following error:

     

    Could you please advise?

    • BA_Pete's avatar
      BA_Pete
      Super User
      3 years ago

       

      It looks like you're trying to use the Web.Contents connector to access SharePoint, which is probably why you're getting a 405 error (due to insufficient permissions to access SharePoint via the Web.Contents connector).

      As per my previous post, you should be using the SharePoint Folder connector with appropriate authentication. It will generate Source code something like this:

      let
    Source =
        SharePoint.Files(
            "https://YourOrgName.sharepoint.com/sites/YourSiteName",
            [ApiVersion = 15]
        )
in
    Source

       

      Pete

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Last time I used Sharepoint Folder connector. Today I tried differently just with a new query and I still got 405 error:

        I am wondering - the link to this SharePoint site looks a bit different as it includes "teams" not "sites". It seems that the file was loaded to ShP via Teams group. Does it make any difference? I'm not a member of this Teams group.

         

        I also tried to use this method for other ShP site, but instead of the content of one of the files loaded there (which is my goal), I just got a list of all files.

        Am I doing something wrong?