In my previous post – available here – I discussed the different connection options with Power BI and SharePoint lists. I discussed the different options for connecting Power BI to SharePoint lists. In this article, I’ll take a deeper dive into Implementation 1.0, which offers powerful access to SharePoint list data—particularly when working with SharePoint document libraries.
Quick Recap: Connection Methods 1.0 vs. 2.0
When connecting to a SharePoint Online list in Power BI, you’ll notice two options: 1.0 and 2.0.
- 2.0 is optimized for performance and is ideal when you only need the standard list data you see in the SharePoint UI.
- 1.0, however, unlocks additional metadata that isn’t available through 2.0.
So, why use 1.0? Because it gives you access to hidden columns and rich metadata—especially useful when connecting to SharePoint Document Libraries.
Why Document Libraries Matter
Let’s look at a practical example.
I’ve created a sample document library with a few uploaded documents. In addition to the default fields, I’ve added two custom columns, “Project ID” and “Project Scope Review Status”
At first glance, you might wonder:
What does a document library have to do with SharePoint lists?
The answer: Every SharePoint document library is backed by a SharePoint list. That list stores all metadata about the documents in the library—metadata that Power BI can access.
Here is an example of the above document library and how it relates to a corresponding list that becomes available for Power BI.
What You Get with 1.0
When connecting to this list using the 1.0 connector, Power BI reveals much more than just your custom columns. You gain access to:
- Document names
- Custom metadata such as “Project ID” and “Project Scope Review Status” that I created in my example
- Direct document URLs
- System metadata such as:
- Created by / Modified by
- Created date / Modified date
- Folder path
- Permissions-related details (e.g., who has access)
Here’s an example of how this looks in Power Query.
In addition to the metadata columns, there are data columns that contain direct links to the documents. Pulling this into your Power BI report now allows you to create direct links to the documents directly from your Power BI report.
Hidden Columns and Rich Metadata
That was a few of the available columns. But by scrolling to the right, there are many more columns – including columns that contain tables, lists, or records of additional detail that can be extracted.
One of the more useful columns available in the 1.0 method is “FieldValuesAsText“. This column contains records that consolidate useful fields in a text-friendly format.
Exploring the contents of this column gives you insights such as:
- Author name
- Creation and modification timestamps
- File path and file type
- Document title
- And much more
And this is just one of many hidden columns. Others may contain nested records, tables, or lists that can be expanded and transformed inside Power Query.
Why This Matters for Power BI
By pulling these rich metadata fields into Power BI, you can:
- Create filters and visuals based on document attributes
- Build direct hyperlinks to documents within your report
- Analyze document ownership, status, or compliance
- Support automated document reporting scenarios
While 2.0 is great for lightweight data pulls, Implementation 1.0 opens up the full metadata structure behind SharePoint lists and libraries—making it ideal for document management and reporting use cases.
If you’re building a report that needs to surface SharePoint document details, 1.0 is the way to go.
Leave a Reply