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

AxioWorks Newsletter February 2024: Partnerships, Upcoming SQList Update, and Summit Invitation

27 Feb 2024

Hello Reader, This February has been pivotal for AxioWorks as we’ve focused on laying strong foundations for the year. Here are the key […]
[read article]

How AxioWorks SQList Aligns With the Priorities of Software Buying Decision Makers

26 Feb 2024

As outlined in the “2024 Global Software Buying Trends” report by Gartner Digital Markets, the criteria that predominantly influence […]
[read article]

Maximising Power BI Reporting from SharePoint: The Strategic Advantage of AxioWorks SQList over Native Connectors

15 Feb 2024

In today’s data-driven environment, making informed decisions rapidly can offer a significant competitive edge. This is where Power BI comes […]
[read article]

AxioWorks Newsletter January 2024: A Promising Start to the New Year!

29 Jan 2024

Hello Reader, We hope this newsletter finds you well and thriving. At AxioWorks, we are embracing the new year with great enthusiasm and are […]
[read article]

Exciting New Partnership and Innovative SharePoint Tools from AxioWorks and Lightning Tools

17 Jan 2024

AxioWorks is excited to announce a strategic partnership with Lightning Tools, a leader in creating innovative SharePoint components. This […]
[read article]

AxioWorks Newsletter November 2023: ESPC23, new partnership, and SQList update!

30 Nov 2023

Hello Reader, Welcome to this month’s edition of the AxioWorks newsletter! We’re thrilled to bring you updates on our recent […]
[read article]

Why Support Legacy SharePoint Versions Like SharePoint 2010?

16 Nov 2023

In the fast-paced world of technology, where new versions and updates are continuously rolled out, a recurring question often arises: “Why […]
[read article]

Why Companies are choosing SQList over Power Automate

30 Oct 2023

In the realm of synchronisation services, AxioWorks’ SQList and Microsoft’s Power Automate (earlier known as PowerApps) have emerged as […]
[read article]

AxioWorks Newsletter October 2023: An exhilarating month.

28 Oct 2023

Hello Reader, As we step into the season of autumn, we are thrilled to share with you the latest developments and opportunities unfolding at […]
[read article]

Why Choose SQList for SharePoint Data Extraction Over DIY Solutions

16 Oct 2023

In the ever-evolving world of technology and data management, the question often arises: “Why should I use a product like #SQList when I can […]
[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

*