Last Updated –
Introduction
In my experience of being a data analyst for 10 straight years, I’ve realized that one of the biggest challenges for data analysts and anyone working with data is combining data from multiple files.
It happens all the time, we receive data in multiple files from multiple sources, and then we have to spend a good amount of time every morning joining that data or merging those files before we can start creating reports and analyzing the data.
That is why, in this tutorial, we’re going to learn how to use a method with Power Query to merge, join, or combine data from multiple workbooks or even worksheets into a single worksheet.
This method is real-time, so once you set up everything, you don’t need to repeat the process every day. It’s a one-time setup, and you’re good to go to combine data whenever there is an update, and we will learn it in depth, step by step, one by one.
This is an ultimate guide that helps you merge or join data from multiple files. These files can be Excel files, PDF files, CSV files, or even text files.
In the beginning, we learn how to join data from the worksheets available in the current workbook. Overall, this guide serves as a complete resource to help you merge and join data from multiple files and worksheets in Excel.
What is Power Query?
Power Query is a data transformation engine that you use to get data from multiple sources, transform that data inside Power Query, and then load it back into Excel. It works in three main steps.
- Connect to a source and load data into the Editor
- Transform data the way you want.
- Load data to a new worksheet.
Every action you perform, starting from connecting to the source data to every transformation you apply, is recorded as query steps.
You never need to repeat those steps manually.
Do I Need to Install Anything?
You don’t need much to start using Power Query, just an Excel version that includes it. Starting from Excel 2013, Power Query is built directly into Excel, and every version from Excel 2013 up to Excel 365 includes Power Query by default. If you are still using Excel 2010, then you need to install the Power Query add-in separately. Other than that exception, Power Query is already built into your Excel version, ready for you to use.
CASE 1: Combine Excel Files from a Single Folder
Let’s talk about a kind of problem, where we have multiple workbooks and need to combine all that data into a single workbook. For example, you might have data for multiple cities, with one separate workbook for each city.
In this case, the challenge is to combine all that data into one main workbook, and that’s the problem we are going to solve here.
Step 1: Connect Folder with Power Query
The first thing you need to do to get started is create a folder anywhere on your system, even on your desktop, and place all those files inside that folder.
In this method of merging or joining data from multiple workbooks, we connect to that specific folder using its folder path. From there, Excel will pick up all the workbooks or Excel files stored in that folder and extract the data from them automatically.
Next, go to the Data tab. From there, click the Get Data drop-down, then choose From File. You’ll see a list of options, and near the end of that list, select From Folder.
When you click this option, a Select Folder dialog box opens. This is simply a browse window where you can navigate to the folder that contains your files. In my case, the folder is on the desktop, so I select it and click Open.
Within a few seconds, Excel scans all the files in that folder and shows a list of files we have in the folder.
Step-2: Get Data from All the Excel Files
Now, here’s one thing that can feel a bit confusing when you connect to a folder using the From Folder option in Power Query. Excel doesn’t immediately show you the data from the files. Instead, it displays a list of all the files in that folder, along with file details such as when each file was created, last accessed, and other related information.
In this window, you’ll also see three buttons: Combine, Load, and Transform Data. Since we still haven’t accessed the actual data from the files, London, Mumbai, New York, and Sydney, we don’t want to load or transform anything yet. This is where the Combine button comes in. It’s specifically designed for situations like this, where a folder contains multiple files rather than raw data.
Click on Combine, and then choose Combine and Transform Data.
Power Query will take a few seconds to evaluate the query, and after that, it will show you the actual data preview combined from all the files in the folder.
Step-3: Preview Files
In a few seconds, once it evaluates the files in the folder, it will show you the Combine Files dialog box. In this dialog box, you will see a preview from the first file.
You can also see the Sample File drop-down, where you can select a file if you want to preview data from a different file. When you click on this drop-down, you can see all four files available in the folder, London, Mumbai, New York, and Sydney.
You can select any of these files to view a preview of data from that specific file. Once you select a file, you will see the sheet name, and on the right side of the dialog box, you will get a preview of the data. This allows you to review the data from each file one by one.
After you are done with the preview and are confident that the data looks correct for combining, select the First File again from the Sample File drop-down, choose the sheet name, and then click the OK button to proceed.
Step-4: Get Data into Power Query Editor
And that is the moment of joy, when the data from all four files in the folder is finally joined in the Power Query Editor. As you can see from the screenshot, the entire data from all four files is now combined.
Along with this data, which has a total of five columns, there is one additional column at the beginning called Source.Name. This column represents the names of the files from which the data is being combined.
If this is your first time seeing the Power Query Editor, you will notice that at the top, we have all the available options. On the right side, you can see the steps of the current query, and on the left side, you can see all the queries that exist in Power Query. In the middle, you have the actual data that is now combined and visible.
Step-5: Delete Source Column
Now, the next thing I’m going to do is delete the Source.Name column, because I don’t need the name of the workbook in my data. I already have a column that shows the city name, so keeping the Source.Name column would create duplication in the data. To do this, I’ll select the column, right-click on it, and then click on the Remove option. This will instantly remove the column from the data.
You’ll also notice that as soon as I remove this column, a new step is added to the Applied Steps section. In the end, you can see a Removed Columns step, which confirms that the first column has been removed from the data.
Step-6: Load Data to the Worksheet
Next, go to the Home tab and click on the Close & Load drop-down, or you can directly click on the Close & Load button. I recommend using the Close & Load option.
The moment you click on it, Excel will load the combined data from all four files into a new worksheet, using the folder name as the name of the worksheet. As you can see from the screenshot, a new green table is created with data from all four workbooks, for all four cities, and the worksheet name is CityData1, which is the same as the folder name I used.
Now, there is one important thing you need to understand about the entire query we just created using Power Query. All the files we used from the folder have the same worksheet name. In other words, all four files containing data from the four cities use an identical worksheet name.
When the worksheet names are the same, these steps become very straightforward and useful for combining or merging data from multiple workbooks. You just need to make sure that all the files are placed in a single folder, and Power Query will take care of the rest.
However, if the data is stored in different files and those files have different worksheet names, then these steps will not work for combining the data. That is why, in the next part of this tutorial, we will learn how to merge or combine data from multiple files when the worksheet names are different. Make sure to read that part as well.
CASE 2: Combine Excel Files from a Single Folder (With DIFFERENT Worksheet Name)
As I mentioned earlier, in this part we’re going to learn how to combine data when the worksheet names are not the same across files. The initial setup remains the same: all the files must be placed in a single folder.
In my case, all these files are stored in a folder on my desktop. Now, I’m going to follow the exact same steps that we used in the earlier part. I’ll go to the Data tab, then to Get & Transform, and click on Get Data.
From there, I’ll select From File and then From Folder. Next, I’ll choose the folder named CityData2, which is where all the files are stored. You can download these files using the link shared in the sample file section.
Once I select the folder and click on Open, Excel will again take a few seconds to evaluate the files and then display the details of all the files available in that folder.
This time, instead of clicking on the Combine button or choosing Combine and Transform Data, I’m going to click on the third button in the dialog box, which is Transform Data. When I click on this button, it will load a list of all the files in the folder into the Power Query Editor.
At this stage, you won’t see the actual data. Instead, you’ll see a list of files along with their details, such as when each file was created, the file extension, the folder path, and other related information.
From here, there is one important step required to access the data from all the files. I’ll select the Content and Name columns, right-click, and then click on Remove Other Columns. This means I’m keeping only these two columns and removing all the remaining ones.
You can see the Content column, which contains the data. Each binary entry in this column represents the data from the file.
Now, to get data from all these four entries, we need to add a new custom column. For this, I’ll go to the Add Column tab and then click on the Custom Column button.
This will open a dialog box where we can insert a new column using a formula. These formulas are similar to Excel formulas, but Power Query uses its own set of functions.
First, I’ll enter the name of the column.
I’ll name it Data, but you can use any name you prefer. After that, in the Custom Column formula box, where the equals sign is already present, I’ll type Excel.Workbook(.
Next, on the right side, you’ll see the Available Columns section. From there, I’ll click on the Content column, and it will automatically insert the column name using square brackets.
Then, I’ll add the closing parenthesis to complete the formula. So, the final formula is Excel.Workbook([Content]).
At the bottom of the dialog box, you can see a message that says No syntax error has been detected. This message is very important because it confirms that there is no issue with the formula you’ve written in Power Query.
Once the formula is ready, I’ll click on OK. Now you can see a new column named Data, and it contains four entries, each labeled as Table.
Now we are at the final step to extract the data from the files. I have the third column that I just added using the custom formula. What I’m going to do next is click on the filter button at the top-right of this column.
As soon as I open this filter, you can see the columns that exist inside this column. Since this is the Data column, I’ll simply click OK, and this will expand the table.
Now, you can see multiple columns in the Power Query Editor. From here, the next step is to select the Data.Name column and the Data.Data column. After selecting these two columns, I’ll right-click and choose Remove Other Columns.
At this point, I’m left with just these two columns.
To get the actual data from all four files, I’ll now click on the Data.Data column again. You can see columns such as Column1, Column2, Column3, Column4, and Column5. These five columns represent the actual data from the four files.
Once I click OK, Power Query instantly shows all the data combined from all four files in the folder.
Now, there is one final touch we need to apply to this data table. You can see that the data is already combined, but the column headings are not correct yet. To fix this, we need to perform two simple steps.
First, we need to use the first row of the data as the column headers. In the top-left corner of the table in Power Query, you’ll find a small drop-down option.
From there, select Use First Row as Headers. The moment you click this option, Power Query converts the first row into the headers of the table. Now you have meaningful column names such as Date, City, Product, Units Sold, and Revenue.
However, you’ll also notice that there are a few extra rows in the data that are actually header rows from the other tables.
To remove these, open the filter for the Date column, uncheck the value that represents the header (such as Date), and then click OK. This filters out the extra header rows, leaving only the actual data.
Now your data is clean and ready to be loaded back into the workbook. Go to the Home tab, click on Close & Load, and Power Query will instantly load the combined data into a new worksheet.
The worksheet will use the same name as the folder that contains the four files.
CASE 3: Combine Multiple Excel Files from Different Locations
Let’s consider a different scenario. We still have four files to combine, but this time the files are not stored in a single folder. Instead, they are saved in different folders or locations on your system. In this case, we can still merge or combine data from these files, but we need to add one extra step to the process.
As we move forward, we’ll also see how to handle situations where the files have different worksheet names. Let’s dive in and understand the exact steps required to deal with this scenario. The first thing you need to do is collect the file paths of all the files into your Excel workbook. This is a manual step, but it’s only a one-time setup. You need to capture the path of each file individually.
If you’re not sure how to get the file path, here’s a simple method. Go to each file, right-click on it, and select the Copy as path option.
Once you do that, come back to Excel and paste the path into a cell. Repeat this process for all the files so that you have the paths listed one by one. As mentioned, this is the only manual part of the entire process. Once all the file paths are listed, give the column a header, for example, File Path.
After that, convert this single-column list into an Excel table. To do this, select the list and use the keyboard shortcut Ctrl + T. Excel will ask you to confirm the table creation; click OK, and the list will be converted into an Excel table.
Next, we need to name this table. Go to the Table Design tab, and on the left side, you’ll find the Table Name input box. From there, you can rename the table. I’m going to name it tblCityData, but you can use any name you prefer. I’m using this name because it represents the type of data stored in these four files.
At this point, your table is ready, and you now have all the file paths stored in a structured format. Now, from here, the next step begins. We’ll go back to the Data tab and click on the Get Data drop-down. From there, we need to select From Other Sources and then click on From Table/Range.
As you can see, we already have a list of file paths stored in a table, and that is the reason we are using the From Table/Range option.
As soon as you click on this option, within a second or two, the same table opens in the Power Query Editor. You can see all the file paths listed in a single column, with the column name File Path.
Now, the next step is to get data from all the files listed in this table.
To do this, we need to use a specific formula that allows Power Query to go to each workbook using the file paths and extract data from them. For this, go to the Add Column tab and click on Custom Column. As soon as you click on this button, the Add Custom Column dialog box appears.
First, enter a name for the column. I’ll use MyData, but you can use any name you like.
In the Custom Column formula box, enter the function File.Contents(. Then, from the Available Columns section, double-click on the File Path column to insert it into the formula.
Finally, add the closing parenthesis to complete the formula.
= File.Contents([File Path])
At the bottom of the dialog box, you’ll again see the message No syntax error has been detected, which confirms that the formula is correct.
Now, click OK. You’ll see a new column named MyData in the Power Query Editor, and each row contains a binary entry. These binary entries represent the data from each file.
Next, move to the MyData column and click on the expand icon at the top-right of the column.
When you hover over this icon, you’ll notice a tooltip that says Combine Files, which confirms that Power Query has successfully read data from all the files listed in the File Path column.
When you click on it, Power Query will take a few seconds to process the data. In the parameter options, select Sheet1 (or the relevant sheet name), and then proceed. After evaluating the query, you’ll see the combined data appear in the Power Query Editor, including columns such as Date, City, Product, Units Sold, and Revenue, pulled from all four files stored in different locations.
Now we are at the final step of this process, which is to load the data back into a new worksheet. Once again, go to the Home tab and click on Close & Load. Excel will instantly load the entire query into your workbook using a new worksheet.
One interesting thing to notice here is that the name of the worksheet where the data is loaded is the same as the name of the table that we initially created to list all the file paths.
The real advantage of this approach becomes clear when you need to add more files that are stored in different locations on your system. If you want to include data from additional files, all you need to do is paste their file paths below the existing ones in the same table. Because we are using an Excel table, it will automatically expand as soon as you add new file paths.
For example, if you originally had four file paths and you add three more, the table will expand from four rows to seven rows. There is no need to change anything in the Power Query. Simply go to the worksheet where the combined data is loaded, right-click anywhere in the table, and click Refresh. Power Query will run the entire query again and load data from all seven files.
This is how you can easily update your combined data whenever you have new files to pull data from.
But What IF Sheet Name is Different in the Each File
In the earlier steps, we worked with an example where all the workbooks had the same worksheet name. That means every file we combined used an identical sheet name. We’ve already covered that scenario in the earlier part of this tutorial. Now, let’s look at what we need to do when all the files listed in our file path table have different worksheet names.
I’ll walk you through the same process again so that you can merge data from all the files without any issues. For this scenario, we need to pause at the step where we add a custom column using the File.Contents function.
As you can see in the screenshot, we currently have two columns in our table. The first column is File Path, which contains the paths of all the files, and the second column is MyData, which we created using the File.Contents function.
Instead of expanding the myData column at this stage, we need to add one more custom column. So again, go to the Add Column tab and click on Custom Column. In the column name, enter Get Data. In the Custom Column formula box, we’ll use the same formula that we used earlier when combining files from a single folder with different worksheet names.
Type Excel.Workbook(, then select the MyData column from the available columns, add the closing parenthesis, and click OK.
Now you’ll see a new column where each row contains a Table value for each file.
Next, click on the expand button at the top-right of the Extract Data column. When the expand dialog appears, click OK, and you’ll see additional columns.
From here, select the Extract Data.Name and Extract Data.Data columns, right-click, and choose Remove Other Columns.
Now, click on the expand button at the top-right of the Get Data.Data column. You’ll see columns such as Column1, Column2, Column3, Column4, and Column5.
These represent the actual data columns from all the files. Click OK, and at this point, all the data from the files is combined.
The final step is to correct the column headings. Go to the top-left of the table and click Use First Row as Headers.
Then open the filter for the Date column and uncheck the Date value, which represents the header rows from the remaining files. Click OK, and your data is now clean and ready.
Finally, go back to the Home tab, click Close & Load, and load the combined data into your workbook.
CASE 4: Combining Multiple Excel Files Saved on Web
We have one more interesting part in this tutorial. This time, we’re going to combine data from the same four files, but instead of being stored on our local system, these files are saved on a web URL.
In this scenario, we have four URLs, and we want to combine data from all of them. Although these URLs belong to my own setup, the approach remains the same.
We’ll use Power Query to connect to these web locations and merge the data just like we did with files stored on our system.
Let’s start with the first step. First, we need to list all the URLs of the files that are stored on the web. Make sure that all these URLs point to files with the same file extension, which in this case is .xlsx.
Next, give this list a column name. I’m using URL, but you can name it URLs or anything else that makes sense to you. Once the list is ready, convert it into an Excel table.
To do this, select any cell in the list, use the keyboard shortcut Ctrl + T, and click OK to confirm.
After converting the list into a table, go to the Table Design tab and change the table name as needed. From the Table Design tab, I’ll change the table name to WebFiles.
Next, go to the Data tab, click on Get Data, choose From Other Sources, and then select From Table/Range. This will instantly open the table in the Power Query Editor, where you can see all the URLs listed.
The next step is to add a new custom column.
Go to the Add Column tab and click on Custom Column. In the dialog box, enter a name for the column, I’ll use WebData.
In the Custom Column formula box, enter Web.Contents(, then select the URLs column from the Available Columns section, add the closing parenthesis, and click OK.
Now, I have a new column named WebData, and each row contains a binary entry for one of the web files.
At this point, we have successfully extracted data from all the web files, but the next step is to convert each binary entry into an Excel table so that we can work with it in the Power Query Editor and eventually load it into a single table in our workbook.
To do this, we need to add one more custom column. I’ll go to the Add Column tab and click on Custom Column. Here, I’ll enter the column name as Extract.
In the Custom Column formula box, I’ll use the formula Excel.Workbook(, select the WebData column from the available columns, add the closing parenthesis, and then click OK.
Now, you can see another column named Extract, and each row contains a Table value for each URL. From here, the next step is to expand this table. I’ll click on the expand button at the top-right of the Extract column, click OK, and the table expands into multiple columns.
Next, I’ll remove the columns that I don’t need. I’ll select the Extract.Name and Extract.Data columns, right-click, and choose Remove Other Columns. These are the only two columns I need at this stage.
Now, I’ll expand the Extract.Data column by clicking on the expand button at the top-right. This gives me five columns, from Column1 to Column5.
I’ll click OK, and now I have all the data from the four different web URLs combined into a single table.
The next step, which is the same as earlier in this tutorial, is to fix the column headings. I’ll go to the top-left of the table and select Use First Row as Headers.
This converts the first row into proper column headers.
After that, I need to remove the extra rows that are actually headers from the remaining files. I’ll open the filter for the Date column, uncheck the value Date, and click OK. Now, only the actual data remains.
The final cleanup step is to remove the first column, which contains the sheet name from all four files. I’ll right-click on this column and click Remove.
At this point, the data is ready. I’ll go to the Home tab, click on Close & Load, and the combined data will be loaded into my workbook in a new worksheet.
CASE 5: Combine Multiple PDF Files
Let’s consider a scenario where you have PDF files instead of Excel workbooks, and each PDF file contains a specific table.
The idea remains the same, to extract data from these PDF files, but the steps are slightly different when it comes to selecting the data to combine.
First, go to the Data tab, click on Get Data, then select From File, and finally choose From Folder. All the PDF files should be placed in the same folder.
Once you click on From Folder, a dialog box will open where you need to select the folder containing all the PDF files.
In my case, the folder is named CityData3. I’ll select this folder and click on Open to connect it with Power Query.
Within a few seconds, Power Query will display the details of all the files in the folder, such as the file name, extension, date created, and other related information.
At this point, I’ll click on the Combine and Transform Data button. Before combining the data, Power Query will show a preview dialog box where I can review each file and check the table inside it.
When working with PDF files, Power Query recognizes data in two ways. The first is at the table level, and the second is at the page level.
For example, if you select Page 001, Power Query will extract not only the table but also other elements on the page, such as headings and additional text.
Since we don’t want those extra elements, we’ll instead select Table 001, which contains only the table data.
Using the Sample File drop-down, you can preview all the PDF files one by one. Once you’re satisfied and have selected Table 001 as the parameter, click OK.
Power Query will again take a few seconds to evaluate each file and then load the combined data into the Power Query Editor.
You’ll notice that the pattern is similar to what we saw when combining data from Excel files.
All the columns from the PDF tables are present, along with an additional column at the beginning named Source.Name, which contains the file names such as London, Mumbai, New York, and Sydney.
Since this column is not required, I’ll right-click on it and select Remove. As expected, a new step is added to the Applied Steps section.
Finally, go back to the Home tab and click on Close & Load. This will load the combined data into a new worksheet, using the folder name as the name of the worksheet.
CASE 6: Combine Multiple Worksheets from the Current Workbook
Now let’s say you have multiple worksheets in your current workbook, and you want to combine the data from those worksheets into a new worksheet.
In that case, you can follow a few simple steps in Power Query to combine data from all those worksheets into one consolidated sheet.
The new worksheet will be dynamic, whenever the source worksheets are updated, you can simply refresh the final query, and Power Query will pull all the updated data again from every worksheet and rebuild your combined output automatically.
1. Convert Data from All the Worksheets into An Excel Table + Rename them using a Keyword (tbl_)
If you look at the data in the example below, I have data from four different cities, like, Sydney, New York, Mumbai, and London. Now I want to bring the data from all four worksheets into a single worksheet in the same workbook.
The first and most important step is to convert the data from each worksheet into an Excel Table. We’ll go to each worksheet one by one and convert the data into an Excel Table. To do that, select any cell inside the data range and use the keyboard shortcut Ctrl + T, then click OK to confirm.
This converts the range into an Excel Table, which Power Query can easily recognize and use. Once you convert your data into an Excel Table, the next step is to rename that table.
Whenever you create an Excel Table, you should give it a meaningful name. In this example, since we have data for different cities, we’ll use the city name as the table name.
Along with that, we’ll add a prefix to keep things organized. We’ll name each table using the format:
tbl_CityName
For example: tbl_sydney / tbl_newyork / tbl_mumbai / tbl_london
Using table names with a common keyword, like “tbl_” at the beginning, has a very specific purpose in the later steps. Make sure you do not skip this naming convention. You can choose any keyword you like, but it must be common across all table names.
If you are using the city name as the table name, ensure the keyword appears either at the start or at the end. The position is not important; the important part is that every table includes the same keyword so Power Query can identify them together in the next steps.
1. Convert Data from All the Worksheets into An Excel Table + Rename them using a Keyword (tbl_)
2. Open Power Query as a Blank Query
Once your tables are ready and renamed, the next step is to load these tables into the Power Query Editor. Now, Excel’s Power Query does not have a direct default option to pull multiple tables from the current workbook at once, so we are going to write a small function to achieve that.
First, go to the Data tab, and in the Get & Transform Data group, click Get Data. Then go to From Other Sources.
When you hover over that option, you will see an extended list of additional sources. At the very end of that list, you will find an option called Blank Query. Click that option, and Excel will open the Power Query Editor with a new blank query.
3. Pull Data from the Current Workbook
Now you have the Power Query Editor open with a blank query, and you’ll see a blinking cursor in the formula bar. This is where we are going to write a function that will pull data from all the tables in the current workbook.
In the same formula bar, we need to enter a formula that allows us to get data from all the tables stored in the current workbook. The formula is:
= Excel.CurrentWorkbook()
Once you type this formula and press Enter, Power Query will instantly pull data from all the tables available in your current workbook.
In the snapshot below, you can see a small table that has been returned by this formula. This table contains two columns: Content and Name.
If you ask where the actual data from the four tables is stored, the answer is, the data for all four tables is inside the first column, Content, where each cell contains a value of type Table.
Now the next question is: if all four tables are stored in a single column as table objects, how do we extract the actual data from them? That’s exactly what we are going to handle in the next steps.
Finally, you need to click on the Filter from the top of the “Content” column.
Once you click the expand (filter) button, you will see all the columns from all the tables that we loaded from the four worksheets. Then simply click OK, and Power Query will expand this single cell into the full combined data from all four tables.
Here we have our data, which comes from all four worksheets in our workbook. Now, we need to give a final and very important touch to this table, one that will make the data we have collected from all four worksheets truly dynamic.
4. Only Filter Tables from Other Worksheets (MOST IMPORTANT)
This part of joining data from all the worksheets in the workbook is really important. I want to point out one key thing here: we’re using the function at the start Excel.CurrentWorkbook(). This function pulls everything from the current workbook, so it correctly picks up the data from our four worksheets.
But a problem shows up when we load the combined result back into the workbook on a new worksheet. Now we don’t just have four worksheets, we have a fifth one that contains the merged data, and Excel.CurrentWorkbook() will also pick that up.
That means the merged table gets included again in the next refresh, which creates a loop where the combined data keeps feeding back into itself. This will continue until we apply a proper fix to exclude that output from being included.
What’s the fix?
Do you remember we have added that keyword in the Table Name?
That was the exact reason we used the keyword tbl_ in the names of all the tables, to make sure we can filter data only from those tables before loading the data into a new worksheet.
Now, go to the last column where the table names are listed and open the filter. Choose Text Filters, select Contains, type tbl_, and apply the filter.
This will return data only from the four tables in the four worksheets of the workbook. By doing this, the merged data that gets loaded back into the workbook is excluded, which breaks the loop of adding the same data again.
Once the filter is applied, you’re ready to load the final data into the new worksheet.
You need to rename all the columns. You’ll notice that each column starts with the word “Content.”, followed by the actual column name. That’s why it’s important to rename the columns. You can rename them one by one, and Power Query will add a single step for this change.
Before loading this data into a new worksheet, the last thing you need to do is right-click on the last column that contains the table names, because we don’t need the table names.
If you do need the table names, you can skip this step and move on to the next one. To delete this column, simply right-click on the column header and select the Remove option.
5. Load Data to a New Worksheet
Everything is ready now. You just need to go to the Home tab, click Close & Load, and Excel will instantly load this data, the table merged from all four worksheets in the current workbook, into a new worksheet.
And here we have our data in a new worksheet.
And if you ever need to add a new worksheet and merge its data into the main worksheet, all you need to do is add the worksheet and convert its data into a table. While naming that table, make sure to include the same keyword you used for the other tables. This is the one key thing you need to remember and take care of when adding more data or introducing additional worksheets with data into the workbook.
Thanks for the insight, I’m still new to Power Query.
Well I have a problem. So, I have 30 files, and I have unwanted rows in every file from row 1-5 (it’s company profile such as address, phone number, etc. which I don’t really need). After transforming data, I use Remove Rows -> Remove Top Rows -> Number of Rows : 5. The problem is, it only works on the first file, the second file and onwards still have these 5 unwanted rows.
I also tried Remove Alternate Rows, but it requires “Number of rows to keep”, which I can’t fill it because every file has inconsistent number of rows.
I also tried it in Power Bi but the result is the same.
Please help
Thanks a lot, i’m the luckiest when i found this web!
Thank you this was helpful. However, I need to combine passsword protected excel files into single file. Any tips/suggestions are appreciated.
It was very helpful. I want to merge data from 37 different excels and with the help of your information it happens in less than 5 minutes. Thanks a lot.
Joy
Really appreciated. Thank you.
Thank you so much Puneet. It is very helpful. I will be looking at your other tutorials too. Thanks again
That was great – thanks a million!
Thanks so much for this, Puneet!
My version of Excel is limited on my work laptop but I have Power BI Desktop. Will the Power Query functionality inside Power BI essentially work the same way?
Yes 🙂
Lots of files downloaded from processing database. unfortunately all sheet / tab names are unique. So i would have to go into each sheet to modify sheet name in order to successfully use this method. Think i’ll leave them as csv and just use python… Thanks anyway. always good to know.
THANK YOU SO MUCH
Very helpful. Thank you so much for this!
Thank you!
“As you said, POWER QUERY is real and if you frequently use to combine data from multiple files then you must use this method……as it’s a ONE-TIME setup”
How can I refresh the combined data when I have more files or want to delete 1 old file. eg.: when I add 1 more file “EAST2” and delete file West, and I don’t want to do all steps again to merge these new group of files (East2, East, South, North). Are there any ways to refresh combined data when source files update like in PBI
“when I have more files or want to delete 1 old file” that won’t create a problem but the data needs to match the steps you create in the query. For Example: when the format of the data changes in a new file that breaks the query steps in some way. But that can also be tackled down, you just need to create a dynamic query. good day.
Thanks alot for this team
Hi, I used the method mentioned on WIN 10 machine and found the the content in the file is lost with error while opening “Problems During Load”, please advice.
Can I have that screenshot on puneet-at-excelchamps-dot-com / puneet-at-gogia-dot-me
(I received an DataFormat.Error) External table is not in the expected format.
Joy
Hello!
Thank you for the article. I’ve tried to merge data from multiple csv files where you don’t have the same name for the sheets and results an error.
“DataFormat.Error: External table is not in the expected format.
Details:
Clienti_20220401.csv”
Do you have a solution also in this case?
JOY