File on ShP site as a data source

Question

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 seem to be not suitable for this case.Could you please advise how to add such file as data source to PowerBI desktop?Thank you in advance!&nbsp;

ba_pete · Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
In Power Query, use the SharePoint Folder connector. The URL to enter will be something like this:
https://orgname.sharepoint.com/sites/sitename
&nbsp;
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.
&nbsp;
Pete

anonymous · Answer

Thank you for your reply. I tried this method, but unfortunately, I got the following error:&nbsp;Could you please advise?

ba_pete · Answer

&nbsp;
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
&nbsp;
Pete

anonymous · Answer

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.&nbsp;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?&nbsp;

ba_pete · Answer

&nbsp;
Teams is based on the SharePoint platform, the same as OneDrive for Business, so any Teams site/channel created gets a SharePoint back-end created to provide a platform for it.
Without being able to see your actual SharePoint it's difficult for me to say whether you're using the correct URL or not, but I've not seen a URL from SharePoint contain 'teams' before, I've only seen it contain 'sites' or 'personal/identifier' (for OneDrive).
If you can access the file location on SharePoint you should be able to access the file via Power Query, providing you use the same credentials and apply them at the correct level i.e. at the '~/sites/YourSiteName' level. The 405 error can also be an indication that you don't have permission to access that URL, so make sure you can access the SharePoint destination first. It may also be the case that you need to be given permissions for the Teams site as well, but I don't recall ever needing this myself.
&nbsp;
This list of files is just a table, the same as any other, so you can filter it to find the file(s) that you want. Once you've identified the file(s), you can either click the green 'Binary' word in the [Content] column to expand a single file, or hit the two down arrows at the top of that column to combine multiple files.
This is a standard way to connect to SharePoint. It means you only need to authenticate once at the highest level, making the connection highly reusable, and also means that you can select your desired files through logic, making it dynamic too.
&nbsp;
As alluded to above, I can't really help a lot more without being able to see your SharePoint/Teams setup or understand your permissions level I'm afraid.
&nbsp;
Pete

Forum Discussion

File on ShP site as a data source

5 Replies

Related content

swapping data source excel file?

Transfer file to sharepoint site from Lakehouse

Data source

Import multiple files from external source

excel source file