Have you ever had two files that you wanted to vertically “stack” together into one in Excel? How did you do it? I’m guessing that short of some complicated script you just copy-and-pasted them together. Not a particularly elegant or repeatable process.
Let’s look at how to solve this problem in a fully auditable, easy-to-execute manner with Power Query.
Understanding the data
For this demo we’ll be working with two csv files consisting of records from those Baseball Hall of Fame nominations that were and were not approved. You can download them along with the solutions workbook here:
Loading the files
Open a blank Excel workbook. We want to load both hof_inducted.csv and hof_not_inducted.csv into Power Query; let’s start with the former. On the home ribbon, go to Data > Get Data > From File > From Text/CSV:
Navigate to the location of your hof_inducted.csv file and connect it to Power Query. From there, select Load To > Only Create Connection like so:
Great! We are now connected to this CSV file via Power Query. Go ahead and do the same thing for the hof_not_inducted.csv file.
Appending the queries
Now that both files are loaded, we are going to append them in the Power Query Editor. To reach the editor, head to Data on the ribbon, then Queries & Connections. Right-click on hof_inducted and select Edit.
Once you are in the Power Query editor, head to its Home tab on the ribbon and select Append Queries > Append Queries as New:
You will now see an Append menu asking which tables to append into a single table. Choose hof_inducted and hof_not_inducted:
Loading & validating the results
Nice work! You have appended the two tables into a third, new query called for now Append1. Go ahead and rename it to something like hof_append by typing over the current name in the Query Settings area:
From here you can send it over to Excel by clicking Close & Load on the Home tab.
If the append went right we should have the same number of rows in the append table as the first plus the second tables. An easy way to confirm that is by actually loading hof_inducted and hof_not_inducted as tables into the workbook. To do this, go back to the Queries & Connections menu and right-click each query, then go to Load To > Table and load each to a new worksheet.
Get your calculator (or another worksheet) out! 323 + 3,868 = 4,191. Successful append.
You can always change the query back to Connection Only, and I would suggest doing that to avoid confusion about the source data versus the appended data.
Nice work appending two files together in Excel the graceful way using Power Query. Have you had to handle this operation before? How did you do it? Do you see this coming in handy? Let me know in the comments.
Leave a Reply