Hi, I'm Tom Blessing. I'm an expert at helping small to mid-sized companies adopt Power BI. I'm also a recovering investment banker, with deep experience applying technology to otherwise manual data problems. I have dual degrees in Finance and Technology Management from Indiana University.

Resources to help build your fundamentals: Learning Power BI? Data fundamentals are critical for this process to be easier. I've assembled a few DataCamp courses to help. I earn a small commission if you click and purchase.

Introduction to Power BI course: Master Power BI basics and learn to build impactful reports.

Exploratory Data Analysis in Power BI course: Learn about Power BI's Exploratory Data Analysis (EDA) and enhance your reports.

Introduction to SQL course: Learn how to create and query relational databases using SQL in just two hours.

Introduction to DAX course: Learn the fundamentals of Data Analysis Expressions (DAX) such as calculated columns, tables, and measures.

Database Design course: Learn to design databases in SQL to process, store, and organize data in a more efficient way.

Chance are high that you have data stored in SharePoint. Let me walk you through the steps to efficiently connect Power BI to information saved in a SharePoint folder. 

There are 3 types of SharePoint connections in Power BI: Folders, Online Lists, and Lists. I'll specifically focus on the SharePoint Folder connection.  

The capabilities here are very powerful -- but only if you unlock their potential by knowing the limitations.

When you link to a SharePoint folder, you gain the ability to pull all files in that folder into the Power BI environment, which you can then combine into a single table.

The limitation is that, to combine multiple files, the schema of the tables in the SharePoint folder needs to be uniform for Power BI (and Power Query) to successfully append the data. This means that all the columns need to be labeled the same way and all the data types need to be the same. 

Use case?

You might have a situation where a Power Automate flow drops a periodic excel report into a SharePoint folder.

So, over time, you've accumulated 50 (or 500) files that need analyzed, all of which are identical in structure. This is a perfect situation to use the SharePoint folder connection.  

When you use this connection, PowerBI makes the connection through the use of Binary files. You'll see rows of binary files for each file you have in your SharePoint folder. 

Why is this important? Because this is NOT your data. It's metadata. It lets you see and manipulate your files before pulling in the enormous amount of data they contain.

Each row represents a file in SharePoint. You need to select which files you want PowerBI to read and then use PowerQuery to combine them.

If you want 1 file, fine. If you want 50 files, that's ok too, as long as the files are structured uniformly. 

The end result of each SharePoint Folder connection is a single table with data from however many files you choose to combine. 

 Ok - When is the 'Connect to SharePoint Folder' connection type less favorable?

It's kind of a hassle to use this method to connect to a single SharePoint file. I think it's much more efficient for multiple files. If it's just one you're after, the easier way, in my opinion, is saving the SharePoint Excel file to your local machine, then connecting to it using Get Data > Excel Workbook. Then, once it's connected, go into File > Options and settings > Data source settings, and change the URL it's referencing to the URL of the file on SharePoint. 

Back to the topic at hand. Here's how to connect to a SharePoint folder, for the purposes of combining a bunch of files. 

Step 1: Open Power BI Desktop

1. Go to 'Get Data' in the ribbon.

2. Click 'More'.

Step 2: Get Data Window

1. Click on 'SharePoint Folder'. If you don't see it, type it into the Search bar.

2. Click the Connect button. 

• Next, copy the URL from your SharePoint site into the box that pops up. Click Ok. Be sure to paste only up to the first word after the "sites/" portion of the URL. It wants the top level folder only. 

• Authenticate: Select "Microsoft Account" and then "Sign In". Once you're signed in, click "Connect". 

• Binaries: Next, you'll see metadata for all of your SharePoint files in that drive. I whited out all my file names so you can't spy on me. Select 'Transform'. When you enter PowerQuery, that's where you'll be able to select which files you want to load and combine.
  • If you select 'Combine' on this window, you'll try to combine all 10,000 files on your SharePoint, which won't work because they're not all structured the same way
  • If you select 'Load' on this window, you'll try to load all 10,000 files on your SharePoint, which is bonkers. Don't do that.
  • Selecting 'Transform' allows you to pick and choose. 

• PowerQuery: Now that you can see all of your files, it's up to you to decide which ones you want to keep, combine, and load. Use the "Remove Rows" drop down to select the rows from the bottom, from the top, or in a range to remove.

• Last Step! Click "Combine Files". This will combine data from all of the Binary files you've retained in your PowerQuery table into a single usable table back in Power BI that you can use to build visualizations. Be sure to click 'Close & Apply' when you're finished to get back into Power BI. 

Step 3: Data Modeling and Reporting

1. Once the data is loaded, you can start building your data model within Power BI. Define relationships, create calculated columns, and design your visuals to present the data effectively.

2. Build your reports and dashboards using the SharePoint data you've imported.

Step 4: Data Refresh

1. Schedule data refresh in Power BI Service to keep your reports up-to-date. Make sure your SharePoint folder is accessible to Power BI for automated refresh.

2. Monitor data refresh for any errors or issues and set up email notifications for refresh failures if required. 

That's it! Now you know how to connect Power BI to SharePoint folders. Great approach for connecting to files that are maintained and accessed by multiple people in an organization.