Power BI: How To Connect To SharePoint Online

In this video, Jonathon Silva walks through how to connect Power BI to SharePoint Online so users can pull cloud-based data into their reports more efficiently. He explains how SharePoint can serve as a central storage location for files, folders, document libraries, and lists, then demonstrates how to connect that content directly into Power BI and shape it in Power Query for reporting.

The video focuses on two practical connection methods:

• Connecting to a SharePoint folder or document library
• Connecting to a SharePoint Online list

By the end of the tutorial, viewers can understand how to import SharePoint data, clean it up in Power Query, and prepare it for use in a Power BI data model.

Why Use SharePoint with Power BI?

Jonathon Silva explains that SharePoint is a valuable cloud-based storage option for teams working with data. Instead of storing files locally, organizations can keep data in SharePoint and connect Power BI directly to it. This setup helps make reporting more dynamic and centralized.

Using SharePoint with Power BI can help users:

• Access data stored in the cloud
• Connect to multiple file types in one place
• Build refreshable reporting workflows
• Standardize how files are combined and transformed

Connecting to a SharePoint Folder

The first example in the video shows how to connect Power BI to a SharePoint folder. Jonathon Silva begins in Power BI by opening Get Data, searching for SharePoint connectors, and selecting the folder option.

One of the most important lessons in this section is the correct use of the SharePoint URL. He points out that Power BI requires the site root URL, not the full path to a specific folder or list. If users paste a URL that points directly to a folder, Power BI will return an invalid URL warning.

To fix this, he demonstrates how to navigate back to the SharePoint site homepage and copy only the main site address. Once the correct site URL is entered, Power BI accepts the connection.

Signing In and Previewing SharePoint Content

After entering the proper URL, Power BI prompts the user to authenticate. Jonathon Silva notes that users should sign in with their Microsoft account rather than choosing Anonymous access. This process uses the same Microsoft credentials tied to SharePoint Online.

Once connected, Power BI displays a preview of the available files. In his example, the folder contains Excel, CSV, and text files. The preview also includes useful metadata such as:

• File extension
• Date accessed
• Date modified
• Date created
• Folder path

This preview allows users to decide whether they want to load the data immediately or open it in Power Query for filtering and transformation.

Using Power Query to Filter and Combine Files

Jonathon Silva recommends choosing Transform Data when additional shaping is needed. In Power Query, he renames the query and begins filtering the imported SharePoint content.

He demonstrates two useful filtering methods:

• Filtering by folder path to focus on a specific document library or folder
• Filtering by file extension to keep only certain file types, such as Excel files

After narrowing the results to Excel files only, he uses the Combine Files option from the content column to merge multiple files into a single table. This is especially helpful when all files share the same structure.

He also explains an important benefit of Power Query: applied steps are reusable. That means future files added to the same SharePoint folder can follow the same transformation logic automatically during refresh.

Removing Nulls the Smart Way

When the Excel files are combined, some extra null rows appear. Jonathon Silva explains that users could remove these nulls directly from the combined table, but a better long-term approach is to clean them in the Transform Sample File query.

By removing nulls at the sample-file level, Power Query applies that cleanup logic to every matching file in the future. This makes the workflow more scalable and reduces manual cleanup later on.

This part of the demonstration highlights an important Power BI best practice: when working with combined files, make reusable transformations at the sample-file stage whenever possible.

Connecting to a SharePoint Online List

The second half of the video focuses on SharePoint Online lists. Jonathon Silva uses the same site root URL as before, but this time he selects SharePoint Online List as the data source from Power Query.

After connecting, Power BI displays all lists associated with the SharePoint site, including system-generated ones. He then selects the specific list he wants to use.

Once imported, the list includes many extra SharePoint-generated metadata columns. Since most reporting scenarios do not require all of them, he demonstrates a quick cleanup method using Choose Columns.

Instead of removing unwanted columns one by one, he selects only the needed fields, in this case:

• Title
• State

After that, he confirms the data types and finishes the transformation. This leaves a much cleaner table ready for modeling and reporting.

Final Takeaway

Jonathon Silva shows that connecting Power BI to SharePoint Online is a practical way to work with cloud-hosted data. Whether the source is a folder, document library, or SharePoint list, Power BI and Power Query provide flexible tools for connecting, filtering, combining, and preparing data.

The video gives viewers a strong introduction to:

• Using the correct SharePoint site root URL
• Authenticating with Microsoft credentials
• Filtering files and combining structured data
• Cleaning data efficiently in Power Query
• Importing SharePoint lists and removing unnecessary metadata columns

For Power BI users looking to streamline cloud-based reporting, this tutorial provides a simple and practical starting point. 

 Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Power BI and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.