How to merge multiple Excel files with Power BI

This post addresses a common scenario where a business unit may have multiple copies of the same Excel file that they need to aggregate into a consolidated data set for reporting. We’ve seen business units use this approach for status reports, sales figures, and issues management.

SharePoint Document Libraries and Folders

In order to understand the solution presented, you must understand how the SharePoint Folder connector retrieves information from your SharePoint site.

The connector treats each site as if all documents stored within one document management store. It doesn’t matter if your documents are in different document libraries within the site and organized in folders within the document library. To the connector, the document library name and the folder name are just labels. Therefore, if you are working within a single site, you don’t have to do a lot of work to retrieve all the documents.

If you are retrieving documents from multiple sites, we recommend extracting the information from each site into its own dataset and then merging the final results.

You Must Have Consistency in Your Excel Files

There’s a favorite scene in Toy Story 2. Andy plays the Evil Doctor Porkchop and asks, “You must choose, Sheriff Woody. How shall she die? Shark, or death by monkeys?” Like Woody, you must choose which way to implement your Excel files as this impacts the ability of Power BI to aggregate the data.

Two Requirements for This to Work

First, choose whether you are you using tabs or tables within Excel? You can’t mix the two easily and we are showing you the easy way in this post.

Tabs

Tabs are the easiest to implement since you can take what you have today. They require:

• All tabs in all spreadsheets to aggregate have the same name
• The first row of the tab will be treated as the column headings for your data set

If you use tabs, everything on the tab will be exported. You must be careful to monitor for the existence of extraneous data in the data set.

Tables

Tables are a preferred way to format your Excel data as they create a structured dataset within the tab. This eliminates the extraneous data issue that can be caused by people putting notes in random cells. It also allows you name your tables with recognizable names. To use this feature, see this https://support.office.com/en-us/article/create-and-format-tables-e81aa349-b006-4f8a-9806-5af9df0ac664

Tables require:

• All tables in all spreadsheets to aggregate have the same name
• The heading row of the table will be treated as the column headings for your data set

Second, you will need consistent column names across your Excel spreadsheets, no matter if you are using tabs or tables. Otherwise, you will get a new column for each column name mismatch. If you have Col1, Col2, Col3 in File 1 and Col1, Column2, Col3 in File2, when you merge them, you will get Col1, Col2, Column2, Col3 in the final data set.

User Scenario

Within SharePoint, we have nearly 200 spreadsheets where we need to aggregate the spreadsheets into one consolidated dataset. We also need to keep the folder path of the file so that we know where the file comes from since there are duplicate names.

The Power BI Merge Process

In this video, you’ll see how to connect to a SharePoint site, select the files to merge, and how to retain the folder name.