Combine all the files in a folder into a single table using Power Query
Part one of a two-part series of blogs

Sure, you can use Power Query (or Get & Analyze, as it's now called) to load and transform data, but did you know you can loop over all of the files in a folder, combining their data into a single set of results? This blog explains how to do this for CSV files, and gives a lead for how to proceed with Excel ones.

  1. In Power Query, load and combine all of the files in a folder (this blog)
  2. Combining CSV Files in Power Query / Get & Analyze

Posted by Andy Brown on 21 February 2017

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

In Power Query, load and combine all of the files in a folder

Or in Get & Analyze, as Microsoft have winsomely renamed Power Query in Excel 2016.

The Point of this Blog

So imagine you have a folder like this:

Four files in a folder

You have a folder which contains four CSV files, each of which contains different products.

 

Actually, you don't have to imagine this; you can download the files used for this blog here.

As an example, here's what the No legs file contains:

No legs animals

A list of products which have no legs (we're basically talking snakes and fish).

This is what the file would look like if you opened it in Excel:

The Excel workbook

In Excel it's easier to see the structure of the file.

If all goes well, we should be able to combine the data together to get a single table in Excel:

The four files combined

Power Query (sorry, Get & Analyze) has combined the rows into a single table.

A word of warning

Before I continue, a caveat - I probably wouldn't do it this way.  I can think of two alternatives:

Method Notes
VBA Write a macro to loop over the files in Excel, opening each one up and pasting its contents onto the end of a list.
SSIS Create a package in Integration Services to loop over the files in the folder, appending the contents of each onto the end of a workbook.

That said, the method shown is actually pretty simple - the problem is that it only works for CSV files, and not Excel workbooks.

Doing the same thing in Excel

It's much easier if you can combine CSV files.  If this isn't an option, there are two ways forward:

Way forward Notes
Convert files to CSV Open your Excel workbooks one by one, and save each as a CSV file.
Extend this blog to cover Excel You can write a custom query in the Power Query Formula Language called M, to combine Excel workbooks.  It involves writing a function in M taking parameters, and is anything but straightforward.  The best explanation of how to do this that I've found is here.

And with all those explanations and warnings, it's time to show how to solve this problem!

  1. In Power Query, load and combine all of the files in a folder (this blog)
  2. Combining CSV Files in Power Query / Get & Analyze
This blog has 0 threads Add post