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

Build your Apps in MS Teams with MS Dataverse for Teams

27 Jun 2022

MS Teams has become one of the most widely used tools for team collaboration and communication in organizations using Microsoft echo-system for […]
[read article]

Dataverse: A one-stop-shop for your App data storage and management

24 May 2022

Data is at the centre of any business application development and is often the deciding factor for which tech. stack to be used for the development. […]
[read article]

AxioWorks Newsletter April 2022: Still working on the next release of SQList…

30 Apr 2022

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]

What’s coming in Power Platform Release wave one of 2022

21 Apr 2022

Due to the ever-rising popularity of the Power platform, Microsoft continues to focus on improving the usability and versatility of the Power […]
[read article]

AxioWorks Newsletter March 2022: Working on the next release of SQList

30 Mar 2022

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]

How to Efficiently Develop and Deploy Solutions in Power Platform

20 Mar 2022

With the Emergence of Low Code to No-Code development platforms, developers have been ever so busy developing solutions utilizing those platforms […]
[read article]

AxioWorks Newsletter February 2022: A good month

28 Feb 2022

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]

Five most awesome features in MS Lists

20 Feb 2022

Microsoft Lists previously known as SharePoint Lists have been widely used for maintaining and storing data in SharePoint and its usage has grown […]
[read article]

AxioWorks Newsletter January 2022: Hello 2022, good bye Covid (we hope)

31 Jan 2022

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]

What went down at MS Ignite 2021: Power Automate and Power Virtual Agent Edition

14 Jan 2022

When we talk about the biggest annual tech conferences of the year, Microsoft Ignite comes to mind where not only do we get a view of all their […]
[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

*