Power Query for Excel: Combine multiple files of different file types

Guest Post by Miguel Escobar – Twitter | Youtube | [link removed due to 404] Blog | Website

Power Query Magic:  The Ultimate and easiest way to consolidate multiple tables, sheets, text and/or csv files
(Click for Full-Size Version)

At some point in the life of an Excel user, we have all faced a similar dillemma. How can I combine multiple sheets, tables, csv or txt files? (can I combine them all together??)

How we used to solve this scenario

Back in the day (before Power Query) we actually had some ways to do so but they were not so user-friendly and they relied heavily on coding or some tedious way of doing it. The most common ways were:

1. Using SQL Statements to join multiple files
2. Creating a VBA code that will do the job for me
3. Going with the tedious way of combining the files manually (perhaps with Excel or Access)

But now we have an easier and optimized way of doing this..let’s find out how

Intermission: How Power Query changes the game

Before we start, let me say this out loud – Power Query is the best tool that Microsoft has created for Excel in the last 5 years. It’s like having an user-friendly interface that creates some wicked cool ultra optimized VBA code for me without me needing to learn any VBA at all

The target audience that Power Query has is much broader than that of Power Pivot. Mainly because Power Query focuses on cleaning, enriching and preparing your data – which is something that most Excel users do on a daily basis – and because of its ease of use.

Don’t get me wrong. I love Power Pivot and always will, but in order for my Data Model to have the most optimal shape I’d go through the Power Query experience rather than the DAX experience for now.

Ok – had to get that off my chest. Let’s get back to our post.

Consolidating / Combining multiple files with Power Query

So far I’ve already blogged [link removed due to 404] here and [link removed due to 404] here and created a few youtube videos explaining how to combine multiple tables and / or sheets from Excel files and Ken Puls also did his thing with the csv files.

Now its time for this to reach a new level, so I want you, the reader, to download this zip file that contains the needed files for you to test this out. I call this the Ultimate Combination.

You’ll download a zip file that has the file with the PQ Query ready to go and a folder with the sample files

• Now, let me guide you through the process on how to use this!

Step 1: Extract the files and find the Query that does the magic

Let’s see what’s in that zip file:

What you’ll get inside that zip file

Now that we know what’s in it, let’s unzip that and once you do that then we’re going to open the file with the name “Ultimate Combination.xlsx” that we saw on the image above.

Note: Before you open that file, please make sure that you’ve  installed Power Query and if you haven’t yet then you can click here to download Power Query.

Once you open that file, please navigate to your Power Query ribbon and then click on Workbook Queries. You’ll be able to see one query on the query pane to your right like this:

Step 2 (final step): Point it to your folder

Now that we found the query, you can right click on it and hit Edit so we can take a look at it.

Now you’re going to see an error on the top of the query that would look like this:

and the reason behind this error is that it is actually pointing to a folder on my local machine. In order to fix this we’re going to have to change that folder somehow and here’s how:

and then click on browse so you can navigate through a friendly “choose folder” window and select the folder that you unzipped before with the name of “Best Way to Combine”.

Now just let Power Query do all the magic and, when it finishes, navigate to the final step called “Expanded”. You’ll see a table that will look like this one:

(consolidated table)

And in this consolidated table you’ll first see the column of the name of the file, then the extension of that file and then, if it was an excel file, you’re going to get information about what object it was from that excel file like what sheet or what table (Sheet Name, Item Name & Kind). You can read more about this on my [link removed due to 404] previous blog post here.

After those columns, you’re going to get all the columns that were found on your files except for the last column which is called Total Rows that shows the total number of rows that were consolidated from that specific file or object. If you continue scrolling down then you’re going to see the result of combining all your data.

You can right click and delete the columns that you don’t need or filter the data as desired and then hit Close & Load so you can load that table into Excel or Power Pivot directly.

Now we can celebrate

Things to take in consideration

I’ve written the code so it can read txt files as delimited by a comma by default but you can change that by changing the code found “The Formula” step like this:

This query is consolidating anything and everything, so if some files have some columns but the others don’t then we will import that column and leave blank values for the files that didn’t have that column 

By reading the previous consideration, you might have guessed that the query is dynamically selecting all the columns available through all the files. This is an advanced topic and you can bet that we’ll cover it in our book (read at the bottom of this page for more info). To see the list of columns that we found from all the files you can look at the formula bar found on the MyList step

Here’s a cool feature, you can actually check the data that is about to be combined before it happens and see how many rows you’re about to combine. You can check this by going to the “Here we go” step and see this table for yourself

On a final note, you can actually just take the bits of M code that you need and totally adjust it to your needs. This just proves the flexibility and ease of use that Power Query has and, remember, this solution works for both Excel 2010 or Excel 2013 (with Power Query installed, of course).

Interested in Learning How to Do this Kind of Thing?

Our New Online Power Query Course just launched this year. You’ll learn Power Query from the best in the business, two Excel MVPs Ken Puls and Miguel Escobar. We’ve included 7 Hours of Self-Paced Video over 31 Courses, with lots of example files, and a Digital Badge powered by Acclaim.

CLICK HERE to claim your spot.

New Power Query Book coming out soon!

A book written by Ken Puls & myself. If you enjoyed the approach that Rob took with his book and how practical it is then you’re going to LOVE this one as well.

Be sure to check out Ken Puls’ blog and [link removed due to 404] mine for some cool tricks with Power Query and things that you might expect to be covered in our book.

Also expect an upcoming blog post of mine in dissecting the M code written for this solution.

Click on this image to pre-order the book!

Special thanks to Avi and Rob for this opportunity to post on this blog.

Avi’s Note: Miguel, thanks to you for your post. M is for (Data) Monkey is going to be my GO-TO book for Power Query as soon as it comes out!