Microsoft’s Power BI SharePoint 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.
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).
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”.
(The result of “Copy path” is: “https://bluegranite1.sharepoint.com/training/powerbitraining/Materials – Day 2 – Intermediate Power BI/Source Files”)
From the Get Data menu I’ll select “Other – Web”.
In the “From Web” dialog box I’ll paste in the URL that I copied from Excel.
**Notice: it’s just the folder path. We need to manually type in the name of the file.**
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.
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)
The URL I’m going to use is the site-level URL – i.e., no sub-sites.
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.
The 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.
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.)
Let’s use the SharePoint folder data source and the URL from the browser and give it a shot.
Nope, that didn’t do the trick.
OK, let’s try the part just before the “/Forms/”. In this case, “https://bluegranite1.sharepoint.com/training/powerbitraining”.
Nope, that didn’t do it either.
Let’s try one level higher: “https://bluegranite1.sharepoint.com/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.
Let’s use our typical “Combine & Edit” technique and see what happens.
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.
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.
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.
Now we can continue on as though pulling from a network/local folder.
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!
