Category: Article

Getting SharePoint-hosted Excel sheets into Power BI Desktop

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!

#shared: Your new best friend

We’ve all been there…you’re waist-deep in M code, you know there’s some function that will let you transform this list you’ve been working with, but you can’t remember what it is. You can’t use Microsoft’s online documentation because it’s out of date or you’re on an airplane or the wifi is down. What can you do to access all of the function documentation that must be hidden somewhere in Power BI Desktop?

= #shared

Enter this little snippet into the Formula Editor’s formula line, and you’ll get a list of all functions available to Power BI–even ones that don’t have online documentation yet.

#shared001.png

Clicking the whitespace next to the Function hyperlinks will show an explanation, syntax, and often an example of how to use the function.

#shared002.png

To make the list searchable, first convert it to a table.

#shared003

Then you can use the filter column functionality to narrow the list down to the functions that you are interested in.

#shared004.png

Being able to access a definitive list of Power BI functions, along with syntax and documentation, all from within Power BI Desktop, has been a godsend to me on numerous occasions. Hopefully you’ll find it helpful too!