How To: Import SharePoint libraries into Power BI and create links to the documents
If you have tried to import from SharePoint to power BI you have probably realised you can import Lists and folders but not libraries. In this post, which is based on a video by Ruth Pozuelo Martinez who is a microsoft MVP we’ll look at the Ruth’s suggested steps for doing the following:
- Export the data from a SharePoint document library into the Power BI desktop
- Create a visualisation that includes the document name and full path to the document document in the SharePoint document library
- Make the full path to the SharePoint document clickable in the Power BI visualisation
- Provide a clickable icon in the Power BI report rather than a long URL in the SharePoint document item
We won’t cover how to publish the data into Power BI online. We’ll cover that in a later blog post. We’ll also cover a shortcut for keeping SharePoint data in Power BI always up to date (rather than 8 times a day using the Power BI gateway)
First of all we are going to go into Power BI desktop and click the “Get Data” button and click “More”
We then enter Share into the search box. You will notice that we can only see 3 options for SharePoint:
As we can see there is no connector to import a SharePoint document library. We can make a few changes to the query in order to achieve this.
First off all, lets choose the folder option and give Power BI desktop the URL of the SharePoint document library:
As you can see we get an error. Power BI just wants the root URL. It just wants the path to our SharePoint site.
Let’s enter that and click ok.
You will then be asked to authenticate with your organisational Office365 account:
Once authenticated, click the “Connect” button:
We are going to clicked on the “Edit” button as we want the contents of a specific library:
We then see the following screen. If you click on source on the right side (highlighted) you will see the Source details (highlighted).
We need to make some adjustments to this:
On the that source we are going to change SharePoint.Files to SharePoint.Content (highlighted) like so:
Click enter and we then see the list of all the contents of that SharePoint site:
Including the shared documents library (highlighted)
Clicking on the “Shared Documents” link you will see the contents of that SharePoint Shared Documents library
As you can see the contents of the document library are now shown including the file name of each of the files in the Shared Document library.
We can scroll across and see the folder path of each of the files:
Click the close and apply button and get all the data in Power BI desktop:
The data starts to import and when complete we can drag the name column from “Query 1” onto the design window:
And we can now see a list of files in the design window. This is the SharePoint Name column from our document library:
To get the full folder path we can open the Query editor by clicking on the “Edit Queries” button (highlighted below)
As we have seen we have the document name and full path:
So we can click Add Custom Column and create a new column called URL which is a concatenation of Folder Path column and the Name column
As you can see a new column called URL has been created which contains the full path to the file in the document library
So when we load that and go back to the designer we can drag across this new URL field
And now we can see the entire path to the files:
What would be really great though is if we could make these items clickable.
This is quite simple! We can do this by going into the data tab (left hand side)
Selecting the Modelling tab (highlighted).
Click the URL column to select it (highlighted)
In the Home Table -> Data Category drop down select “Web URL” (highlighted)
We can now click on the report tab on the left hand side (highlighted) and we can see that the URL is now clickable
What if we just wanted an icon for the URL rather than a really long URL? Another simple change.
- Select the visual (highlighted)
- Select the paintbrush icon under visualisations
- Scroll down and expand “values”
- You will see a “URL Icon” option which will be switched off
Switch the “URL icon” on
So with Power BI you can start do some quite funky reports on latest documents added or by document type. Maybe analysis via content type etc etc.
What the full video: