The topic How to merge files and tables in Excel using Power Query is currently the subject of lively discussion — readers and analysts are keeping a close eye on developments.
This is taking place in a dynamic environment: companies’ decisions and competitors’ reactions can quickly change the picture.
If you spend Mondays copying data from email attachments into a “Master” sheet, you’re not working in Excel—you’re doing manual labor. Power Query fixes that. Master these three core data consolidation workflows to turn hours of copy-pasting into a single Refresh click.
Cleaning data is a classic Power Query use case, but the real power comes when you stop thinking about individual tables and start thinking at the system level. Most Excel users spend a huge chunk of their week copy-pasting data or chasing down the latest CSV.
Power Query helps eliminate this manual labor through three consolidation workflows:
By mastering these, you turn Excel from a static calculator into a dynamic data hub.
Power Query’s Append feature lets you unify several local tables into a single continuous dataset.
The scenario: You have a workbook with 12 separate tabs—one for each month of the year—and you need to combine them to create an annual summary report.
Download a free copy of the workbook used in Workflow 1. When you click the link, you’ll find the download button in the top-right corner of your screen.
Once you’ve checked your setup, use this method to stack them:
You can remove the Name column (by right-clicking the column header and clicking Remove) or keep it, depending on whether you want to track which table each row came from.
Now that all the data is visible, you’re ready to set the data types and load the results:
You now have a single, unified master table on your Summary sheet.
The best part? It’s hands-off. Add a new table, then click Refresh All in the Data tab, and it’s included automatically.
Set these workflows to update automatically so you never have to refresh them manually. Right-click your output table, select Query > Properties, and check the box for Refresh data when opening the file. You can also toggle Refresh every X minutes to keep your data live while you work.
Relational merging in Power Query allows you to pull specific data points from one table into another by finding a common link.
The scenario: You have one table (AgeData) containing employee names, ages, and locations, and another table (DeptData) containing those same names paired with their department and job level. These tables are on separate worksheets in the same workbook, and you want to combine them into one.
Download a free copy of the workbook used in Workflow 2. When you click the link, you’ll find the download button in the top-right corner of your screen, and when you open the file, you’ll see each table on its own worksheet tab.
First, you need to load both tables into Power Query by creating Connections:
Now that both tables are loaded, you’re ready to perform the merge:
At this point, the Power Query Editor opens. You’ll see all the columns from your first table displayed normally, but the data from the second table is condensed into a single new column where every cell simply says “Table.”
Now that you can see the condensed data, you’re ready to unpack it:
You’ve now successfully joined two separate lists without writing a single formula.
Because this is a dynamic connection, you don’t need to manually repeat the merge process. As always, if the source data changes, simply click Data > Refresh All (provided the source tables and column structure haven’t changed).
The From Folder connector pulls data from every file in a directory. It’s ideal for recurring reports like weekly exports or monthly statements.
The scenario: Your coworkers drop weekly Excel files into a shared folder. Every workbook contains a worksheet tab named SalesData, and you need to combine them all into one summary sheet every Monday.
Download free copies of the Sales_Week_1 and Sales_Week_2 workbooks used in Workflow 3. When you click the links, you’ll find the download button in the top-right corner of your screen. Then move both files to a new, dedicated folder named Weekly Reports on your computer.
Before connecting, ensure your incoming files and folders are standardized:
Now, you need to point Excel to your data source, using the Power Query Editor as a security gate to make sure only the correct files get through:
Now you can tell Power Query how to open and stack those files:
Next week, instead of opening a new file to copy-paste, just save it into that folder and click Data > Refresh All.
Power Query removes the need for manual consolidation. Just drop files in the folder and click Refresh All. If you only need to combine simple ranges without transformations, you can use VSTACK and HSTACK in Excel for Microsoft 365 and Excel for the web.
Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.