Oct 16, 2018

Power BI Office Hours: SharePoint Files, Lists, & Folders

Eric Lofstrom Posted by Eric Lofstrom

Microsoft’s Power BI is the go-to tool for organizations looking to quickly gain deep insight. Accessible from pretty much anywhere – desktop, mobile or tablet – the business analytics solution brings data to life with brilliant visuals. During BlueGranite’s interactive monthly Power BI Office Hours, open to all, we explore simple ways to pilot new features with this dynamic platform.

Today we’re looking at how to navigate SharePoint files, lists, and folders. You can follow along with the recorded presentation here.

Power BI Office Hours

In our last Power BI Office Hours session, we previewed a few of the great features in the September 2018 release of Power BI.  We specifically looked at M IntelliSense, Copy and Paste in Tables/Matrices, and Aggregations (preview) (demos begin at 7:39). We had a lot of great questions at the end of the session, as well! Keep those questions coming, and maybe we’ll look at Project Online in the near future.

An important note briefly touched on regarding Aggregations, and more specifically, Composite Models: remember that switching from DirectQuery to Import is an irreversible operation.  It cannot be undone.  So please be careful before changing storage modes willy-nilly.

Our Use Case this month was pulling from SharePoint.  For any of you that have tried (and, like me, initially failed) you know that this can be a painful process if you’re not careful.  In our demo (starts at roughly 21:05) we walk you through importing data from the three popular SharePoint sources: files, lists, and folders.

If you have any questions, please don’t hesitate to contact us directly! Let’s get started.

Using SharePoint Files

To use a file from SharePoint you don’t use a SharePoint connector, you use the Web connector. Once Power BI has connected to the file you choose, it’s smart enough to extrapolate the file type and grab the right navigator dialog box.

The trick is getting the correct URL to the SharePoint folder.  The easiest way to do this is to open the file in question from SharePoint, and grab the URL from there.

In the example below I have a SharePoint folder with a collection of different files. However, I’m only interested in the Geography_Extended file.


First, I’ll open the file in SharePoint, and then open the file from there in Excel (Desktop).

Open in Excel

Once I have the file open in Excel, I’ll get the folder path in SharePoint by selecting File -> Info from the menu.  I’ll left-click the breadcrumb under the file name to copy the path to the Clipboard.  Alternatively I could right-click where it says “Open File Location” and select “Copy path”.

Copy Path

(The result of “Copy path” is: “ - Day 2 - Intermediate Power BI/Source Files”)

From the Get Data menu I’ll select “Other – Web”.

Get Data

In the “From Web” dialog box I’ll paste in the URL that I copied from Excel.

From Web**Notice: it’s just the folder path. We need to manually type in the name of the file.**

File Path

Power BI is now “smart enough” to grab the appropriate connector for the file, and I can continue working with the Excel file just as I would one that is stored locally.


Using Lists from SharePoint

Here I have a list of Regional Directors that I maintain in a SharePoint List. Let’s pull this into our Power BI dataset as well.

SharePoint List

From the Get Data menu I’ll search for “sharepoint” and select the “SharePoint Online List” connector. (If your organization uses SharePoint on-premises you’d select the “SharePoint list” connector)

Get Data SharePoint

The URL I’m going to use is the site-level URL – i.e., no sub-sites.

SharePoint Lists

The Navigator will display all Lists in that entire site independent of what page/sub-site they appear on.

We can search and select the list(s) that we want. I want the “Regional Directors” list.

Regional Director ListThe list has a lot of columns I really don’t care about – so I can eliminate them, just as I would any other data source, and continue about my business. I can also rename the columns to suit my needs, as I would with any other data source.

Choose Columns

Using Folders from SharePoint

Folders in SharePoint can get a little tricky because, like Lists, they exist at the site level as opposed to in the different Pages as we see them in the browser. So how to grab a few files from a SharePoint folder and consume additional files as they are placed in said folder?

We need to get all the folders from the site in question and then start filtering them down. You’ll see how we can consume multiple files in a folder as we go.

I have a folder in SharePoint at the following location (not the true URL here, for illustrative purposes): SharePoint/Training/Power BI Training/Materials Day 2/Source Files. I want to pull any Excel files that start with “Stores” in this folder. (I could also use this technique to pull only one file from a folder, but earlier we showed an easier way to pull just one file from SharePoint.)

SharePoint Folder Data

Let’s use the SharePoint folder data source and the URL from the browser and give it a shot.

Get Data SharePoint Folder

Invalid URL

Nope, that didn’t do the trick.

OK, let’s try the part just before the “/Forms/”. In this case, “”.

Unable to Connect

Nope, that didn’t do it either.

Let’s try one level higher: “”.

SharePoint Training

OK, that did the trick – although now I’ve got many more files than I hoped for. But I’ve got that handy Folder Path column that I can probably use to narrow things down.

Folder Path

Let’s use our typical “Combine & Edit” technique and see what happens.

Combine and Edit

Nope, that didn’t do it. If I look at the screenshot above, I see that the first file found in the Training site is a .vsdx file, which isn’t supported. Let’s just Edit for now and get this data into the Query Editor and we’ll see what we can do there.

Query Editor-1

OK – so we’ve got something in the Query Editor. Let’s narrow things down a bit by filtering down the Folder Path column to just the folder we want and additionally filter to .xlsx files that start with “Store”. Remember that these filter steps will be applied every time I refresh the file, so I only have to do this navigation once.

Power Query Editor

There we go – we now have Power BI looking at just the files I want. I can now manually kick off the “Combine” wizard by clicking the button in the right corner of the “Content” column header.

Combine Wizard

Combine Files

Now we can continue on as though pulling from a network/local folder.

Power Query Editor

Though I wish that pulling data from SharePoint was easier – especially for a SharePoint noob like myself – I hope this helps you on your Power BI journey.

Discover many more Power BI tips and tricks when you check out our archived Office Hours here or join our next Office Hours session on October 25th at 11am EST to learn more about what's new in Power BI, plus get all of your questions answered!
New call-to-action
Eric Lofstrom

About The Author

Eric Lofstrom

Eric is a Principal at BlueGranite focusing on Self-Service and Corporate Business Intelligence solutions. Eric is an expert in all aspects of business intelligence including ETL, Data Modeling, OLAP, Reporting, and Performance Management and has lead teams to deliver solutions across the entire BI project lifecycle including requirements gathering, architecture, design, and implementation. Eric is an expert in the Microsoft BI stack primarily focusing on Power BI, DAX, and M/PowerQuery. He hosts a monthly series ‘Power BI Office Hours’ and frequently leads discovery seminars and in-person training sessions for BlueGranite clients on the Power BI toolset.

Latest Posts

Power BI Office Hours