Our use of cookies

We use cookies to tailor your experience, gather analytics, and provide you with live assitance. By clicking "Accept" or continuing to browse our site you agree to the use of cookies. For more details please read our Cookie Policy.

How To: Import SharePoint libraries into Power BI and create links to the documents

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”

1 Import SharePoint libraries into Power BI and create links to the documents

We then enter Share into the search box. You will notice that we can only see 3 options for SharePoint:

2 Import SharePoint libraries into Power BI and create links to the documents

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:

3 Import SharePoint libraries into Power BI and create links to the documents

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.

4 Import SharePoint libraries into Power BI and create links to the documents

You will then be asked to authenticate with your organisational Office365 account:

5 Import SharePoint libraries into Power BI and create links to the documents

Once authenticated, click the “Connect” button:

6 Import SharePoint libraries into Power BI and create links to the documents

We are going to clicked on the “Edit” button as we want the contents of a specific library:

7 Import SharePoint libraries into Power BI and create links to the documents

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:

8 Import SharePoint libraries into Power BI and create links to the documents

On the that source we are going to change SharePoint.Files to SharePoint.Content (highlighted) like so:

9 Import SharePoint libraries into Power BI and create links to the documents

Click enter and we then see the list of all the contents of that SharePoint site:

10 Import SharePoint libraries into Power BI and create links to the documents

Including the shared documents library (highlighted)

11 Import SharePoint libraries into Power BI and create links to the documents

Clicking on the “Shared Documents” link you will see the contents of that SharePoint Shared Documents library

12 Import SharePoint libraries into Power BI and create links to the documents

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.

13 Import SharePoint libraries into Power BI and create links to the documents

We can scroll across and see the folder path of each of the files:

14 Import SharePoint libraries into Power BI and create links to the documents

Click the close and apply button and get all the data in Power BI desktop:

15 Import SharePoint libraries into Power BI and create links to the documents

The data starts to import and when complete we can drag the name column from “Query 1” onto the design window:

16 Import SharePoint libraries into Power BI and create links to the documents

And we can now see a list of files in the design window. This is the SharePoint Name column from our document library:

17 Import SharePoint libraries into Power BI and create links to the documents

To get the full folder path we can open the Query editor by clicking on the “Edit Queries” button (highlighted below)

18 Import SharePoint libraries into Power BI and create links to the documents

As we have seen we have the document name and full path:

19 Import SharePoint libraries into Power BI and create links to the documents

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

20 Import SharePoint libraries into Power BI and create links to the documents

As you can see a new column called URL has been created which contains the full path to the file in the document library

21 Import SharePoint libraries into Power BI and create links to the documents

So when we load that and go back to the designer we can drag across this new URL field

22 Import SharePoint libraries into Power BI and create links to the documents

And now we can see the entire path to the files:

23 Import SharePoint libraries into Power BI and create links to the documents

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)

24 Import SharePoint libraries into Power BI and create links to the documents

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)

25 Import SharePoint libraries into Power BI and create links to the documents

We can now click on the report tab on the left hand side (highlighted) and we can see that the URL is now clickable

26 Import SharePoint libraries into Power BI and create links to the documents

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

27 Import SharePoint libraries into Power BI and create links to the documents

Switch the “URL icon” on

28 Import SharePoint libraries into Power BI and create links to the documents

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:

Latest articles

We are looking for a Sales and Marketing Manager!

18 Jun 2021

We are looking for someone with experience in the SharePoint and BI arenas to take on the role of Sales and Marketing Manager. You will be able to […]
[read article]

Mover : Only Migration tool you will ever need to move your cloud content to SharePoint

11 Jun 2021

One of the biggest conundrum of any organization planning to move to SharePoint is how to move all their documents stored in various different […]
[read article]

New version of AxioWorks SQList released (7.0.0.0)

04 Jun 2021

A new version of AxioWorks SQList is out today! We have been really busy for the past few months, but here we are with a brand new release of […]
[read article]

AxioWorks Newsletter June 2021: May has been a month of new releases

01 Jun 2021

Hello Reader, Welcome to another exciting issue of our monthly Newsletter! Each month, we try our best to bring to you content that is relevant and […]
[read article]

Form Configuration Editor: Lightweight and Built-in SharePoint List Form editor

16 May 2021

SharePoint Lists provides an easy and robust way of storing and manipulating data in SharePoint.  When you set up a new custom list, you also get OOTB […]
[read article]

AxioWorks Newsletter May 2021: April flew by faster than SpaceX

01 May 2021

Hello Reader, Welcome to another issue of our monthly Newsletter! Thank you for the great feedback on the past newsletters, our team has been working […]
[read article]

Bring Clarity to your SharePoint with MS Clarity

26 Apr 2021

Introduction There has been an exponential growth in SharePoint usage over the past few decades due to its vast set of capabilities to fulfill the […]
[read article]

AxioWorks Newsletter April 2021: A busy month!

01 Apr 2021

Welcome to our second Newsletter! Did you enjoy the first one? Or maybe you didn’t? Either way, do give us some feedback using the links at the […]
[read article]

MS Lists has finally landed in MS Teams

29 Mar 2021

During this Pandemic era, working remotely has significantly increased throughout the world and so is the usage of tools to facilitate collaboration […]
[read article]

AxioWorks Newsletter March 2021: Our first!

01 Mar 2021

We have just sent out our first newsletter and, being it our first one, we thought we dedicate a post to it. All future ones will go out just by […]
[read article]

Get SharePoint and SQL news to your inbox

Stay up-to-date with industry news and trends, SQL and SharePoint innovations and all the latest from AxioWorks by subscribing to our monthly newsletter.

Subscribe to the AxioWorks newsletter

*