Working with Excel files is one of the most common starting points in Power BI consulting and self-service BI adoption. Organizations often maintain sales, finance, HR, and operational data in multiple Excel workbooks. Instead of manually importing each file, Power BI allows you to automate this process, append data dynamically, and create scalable data models for analytics.
This article will show you two easy ways to connect to Excel files in Power BI and demonstrate how to append multiple similar Excel files in an automated way. These techniques are widely used across industries, whether in retail analytics, manufacturing dashboards, supply chain monitoring, or SSRS migration projects to save time and eliminate repetitive tasks.
Connect to Excel Using Excel Connector
Step 1: Either select Excel in the Home ribbon or click on Get data and select Excel.
Step 2: A browse window will pop up. Navigate to the Excel file’s location. Click on the file & select Open.
Step 3: In the Navigator window, select the required sheets & click Load. Clicking on Transform data will take you to the Power Query Editor.
Step 4: Apply transformations of your choice & then choose Close & apply. Your data will be ready to visualize.
This method is ideal when working with a single file—for example, pulling in the latest monthly sales report or P&L statement for analysis. However, if you’re dealing with dozens of files across multiple months, the folder method is more efficient.
Connect to Excel Using a Folder
Step 1: Click on Get data & select Folder.
Step 2: It will open a Folder dialog box. Click on Browse and navigate to the folder which has your Excel files. Select the folder & click OK. Proceed by clicking OK for the Folder dialog box.
Book a Free Consultation to learn how Addend Analytics can automate your Excel-to-Power BI workflows.
Step 3: Click on Transform Data option. It will open the Power Query Editor.
Note: You would have noticed that the Navigator window gives an extra option to Combine the files. We will not be combining it in this section, but you can use it to shorten the steps required to use this feature, which I will be explaining in the next section.
Step 4: In Power Query Editor, when you want to select individual Excel files, you can click on Binary next to the name of the file.
Step 5: Select Data next to the name of the sheet. And once you’re done with other required transformations, hit Close & apply.
This method is useful for organizing files in one folder—for example, storing monthly inventory updates or regional sales files in a shared location.
Combine Similar Excel Files into One Table
Step 1: Follow Steps 1 to 3 from the previous section (Connect to Excel using Folder).
Step 2: In Power Query Editor, click the Combine button from the top-right corner of the Content column.
Step 3: In Combine files window, select a sample file on which you can specify transformations that will be automatically repeated for the other files in the list. (Default selection will be the first file).
Step 4: Select the sheet. Then click on OK.
Note: The Name of the sheet must be the same across all the files that are being combined.
After step 4, you will notice that Power BI has created a bunch of queries & functions by default. In them, there will be a Transform Sample File query (You can rename it later) which will have the basic transformations that Power BI applies according to the structure of the sheet. More transformations can be added to this query & Power BI will automatically replicate it across all the other Excel files.
Get a Demo and see how combining Excel files in Power BI eliminates manual reporting.
Step 5: In the main query, Power BI must’ve pulled all the data from every file present in the folder.
Power BI will also create a column to store the file name to make it simple to identify which row belongs to which file.
Going forward, if you add more files to the source folder, it will automatically start reflecting in the Power BI report after the next refresh.
Using this combine feature, you can connect Power BI to one folder & keep on adding more similar files without ever needing to append manually. A simple use case is placing monthly sales files in a single folder.
Pro Tip: If your folder has a lot of different files & you want to only use files that are relevant to the table, you can filter the list based on the file’s name, type, etc., before combining them together.
Why This Matters for Businesses
· Finance Teams: Automate monthly P&L consolidation across different regions.
· Retail Companies: Append weekly store-level performance data into one dashboard.
· Manufacturing: Combine machine-level logs across multiple plants into one manufacturing dashboard.
· Enterprises Migrating from SSRS: Replace manual report exports with an automated Power BI process.
This method reduces manual effort, improves data accuracy, and ensures your dashboards are always up-to-date with the latest files.
Talk to a Power BI Expert at Addend Analytics to build scalable, future-ready dashboards.
FAQs on Importing Excel Files into Power BI
Q1. Can Power BI automatically refresh Excel files from a folder?
Yes. If you connect via the Folder connector, Power BI will automatically include new files added to that folder after a refresh.
Q2. Do all Excel sheets need to have the same structure to combine them?
Yes. The sheet name and structure (columns/headers) must be consistent across files for Power BI to combine them successfully.
Q3. Can I filter out unnecessary files in the folder before combining?
Absolutely. In Power Query, you can filter by file name, type, or other attributes to ensure only relevant files are included.
Q4. Is this approach better than manually appending Excel files?
Yes. Manual appending is prone to errors and time-consuming. With Power BI consulting best practices, automation ensures accuracy and scalability.
Q5. Can I use this method for CSV files as well?
Yes. The folder method works similarly for CSVs, making it flexible for different source formats.
Importing multiple Excel files into Power BI doesn’t have to be a manual, repetitive task. By using Excel connectors and the Folder + Combine Files method, businesses can automate data ingestion, reduce errors, and build dynamic dashboards for finance, retail, manufacturing, and enterprise reporting.
At Addend Analytics, a trusted Microsoft Solutions Partner, we help organizations streamline Power BI data models, DAX functions, and reporting automation. Whether you’re consolidating Excel reports, migrating from SSRS, or building advanced dashboards in Microsoft Fabric, our experts can guide you through every step.
Ready to transform your reporting workflows? Contact us today for a free consultation.
