In my previous post, I provided an explanation of how to connect Power BI to a SharePoint Document Library using the SharePoint Online List connector (version 1.0). In this follow-up, I want to expand on that by providing specific use cases that demonstrate how this connection can be leveraged to build interactive, metadata-rich reports in Power BI.
SharePoint is a widely used document management platform that offers robust functionality for organizing and maintaining documents. Beyond just storing files, SharePoint enables users to tag documents with custom metadata. These columns appear alongside the file name, providing structured, searchable data that enhances document management. Additional metadata fields can easily be added via the “+ Add column” option.
Let’s take a look at a sample SharePoint document library that I have created which includes two custom columns to track data associated with each document, “Project ID” and “Project Scope Review Status”. As new documents are added, these columns can be updated with information.
So, what does this have to do with Power BI?
Every SharePoint Document Library is backed by a corresponding SharePoint Online List. This list contains all the information and metadata associated with all files and folders within a document library. By connecting Power BI to this list, you can bring in folder names, document names, as well as all associated metadata for use in interactive reports and dashboards.
This unlocks new reporting possibilities within Power BI—such as tracking the types of documents within the library, providing URLs to access the documents, creating slicers based on the metadata, and more.
Connecting Power BI to a SharePoint Document Library
If you haven’t read my previous post on how to connect Power BI to a SharePoint Document Library using the SharePoint Online list connector 1.0, be sure to check that out. That will walk through how to connect Power BI to a document library list.
For purposes of the sample use cases below, I’m going to connect Power BI to the sample document library (image provided above) using the 1.0 SharePoint Online list connector. I also perform cleanup in Power Query to select only a few fields. When you connect to a SharePoint list with the 1.0 connector you will get a lot of columns so cleanup in Power Query is imperative.
I will reduce my data to just the following fields:
- “Id” – a standard SharePoint column
- “ServerRedirectedEmbedUrl” – a standard SharePoint column
- “ProjectID” / “DocumentReviewstatus” – these are custom columns I have added to my document library
- “FieldValuesAsText” – a standard SharePoint column
You may note that data in the “FieldValuesAsText” column shows up as a Record. These records can be expanded on and contain additional data. In my example, I’m going to select only three fields that I want:
- “FileLeafRef” – this provides the filename
- “File_x0020_Type” – this provides the document type (i.e. “docx”)
- “FileRef” – this will be used to create the URL
I now have all the data that I need. However, it’s always a good idea to check other columns that you might find useful. Note: My document library contained other documents, including some folders that I filtered out within Power Query.
Let’s look at some possible uses with this data.
Use Case 1: Add Document Hyperlinks in Power BI from SharePoint
One of the most practical benefits of connecting Power BI to a SharePoint Document Library is the ability to generate clickable links to each document directly in your Power BI report. This allows report users to go directly from a Power BI report to the corresponding document stored in SharePoint—streamlining access and eliminating the need to manually search through folders.
How to Generate Document Hyperlinks
There are several ways to do this, but I’ll describe two. In my query example above, I have two columns I can use. The first is the column called “ServerRedirectedEmbedUrl” which provides a ready-to-use URL within Power BI. The other column is the “FileRef” which just stores the file name (and folder path if this file is contained in a folder/subfolder). To create a full clickable URL, I can simply add a custom column to concatenate my SharePoint site URL with this path.
Once added, you can do the following for each column:
- Go to the Data view in Power BI.
- Select the applicable column.
- Under the Column Tools tab, set the Data Category to Web URL.
Now, in your report visuals (like a table), the links will appear as clickable URLs that open the documents directly in the browser. By default, the full URL will be listed which is very unsightly. Two options include:
- Select the table visual, find the “URL icon” in the format section of the visualization pane and turn this one.
- Instead of the “URL icon” turn the “Web URL” on and create your own custom text using DAX.
In my example, I’ll turn on the “URL icon” option and add these fields to a simple Power BI table visual. The URL icons will be displayed in my table and users can easily click on any document to access it directly from Power BI; no need to go to SharePoint. Below is an example of hovering over a Document URL.
Important Note: The way a SharePoint document opens—whether it launches in a web browser, opens directly in a desktop application like Microsoft Word, or downloads to your computer—depends on both the type of file and the structure of the SharePoint document link. When integrating SharePoint with Power BI, it’s important to understand how different URL formats behave. For a walkthrough of examples, check out my second video below.
Use Case 2: Use SharePoint Metadata to Filter Power BI Reports
Another key benefit of connecting Power BI to a SharePoint Document Library is the ability to pull in and report on custom metadata fields associated with each document. These columns provide context and structure to your document management process—and integrating them into Power BI unlocks deeper reporting and filtering capabilities.
In my example, I have a custom field in my document library called “Document Review Status” which indicates whether a document needs to be reviewed or has been approved. I can now add a slicer to my Power BI report making it easier for users to find documents they need.
Use Case 3: Analyze Document Types from SharePoint in Power BI
When managing a large set of documents across multiple teams or projects, understanding the composition of your document library can provide important insights. By connecting to a SharePoint Document Library with Power BI, you can easily analyze the types of files being stored and how they’re distributed—giving you a clearer view of how your document repository is being used.
In my example above, I’ve pulled in the document type from the “FieldValuesAsText -> File_x0020_Type” field (see picture above). Similar to the previous use case, I may want to add a slicer to select a certain file type (such as Word documents).
Summary
These are just a few sample use cases, but there can be many more depending on your needs. The important point to remember is understanding the hidden metadata that is available when connecting Power BI to SharePoint document libraries via the SharePoint Online list connector.
Also – be sure to check out several YouTube videos I have on these topics including:
Leave a Reply