A colleague presented me with an interesting quandary this week: he is building a set of Power BI reports that get their data from a stack of Excel spreadsheets which live in a folder on our internal SharePoint site. The problem is that the Folder data source only allows local paths, so it wasn’t letting him put the SharePoint folder’s URL. I had a hard time finding a solution on the web so I thought I’d share what worked for me in case there are others in the same boat.
The trick, it turns out, is to use the SharePoint.Contents  function:
SharePoint.Contents("https://myhost.com/personal/myusername/")
This should return a table with the nested hierarchy of the documents/folders available on the SharePoint site. The complete code I used to arrive at the folder of the Excel documents navigates that hierarchy and could obviously be customized to your particular situation / derived procedurally if there is some sort of system that generates the folder names:
let Source = SharePoint.Contents("https://myhost.com/personal/myusername/"), Documents = Source{[Name="Documents"]}[Content], #"Shared with Everyone" = Documents{[Name="Shared with Everyone"]}[Content], myProjectDirectory = #"Shared with Everyone"{[Name="myProjectDirectory"]}[Content], dataDirectory = myProjectDirectory{[Name="dataDirectory"]}[Content] in dataDirectory
The data in the Excel docs can be accessed directly at this point.
I used Windows authentication to get in and it worked great. Depending on how your SharePoint site deals with auth, that should probably work for you too.
And that’s it! Not so hard if you know which function to use. I spent a fair amount of time trying to get in with SharePoint.List (which does show up in the Get Data options but can’t get this sort of data from a SharePoint site), OData.Feed (which is what is going on under the covers but I couldn’t get it to work right), and simple Web.Contents (couldn’t even get the auth flow to work) without much luck.
Also, be aware that if you’ve got Fiddler running in the background to try to sniff out why things aren’t working, SharePoint seems to be able to detect it and instantly starts returning 404’s. So there’s that.
Anyway, post a comment if this worked for you or if it didn’t!