Combine all the files in a folder into a single table using Power Query
Part two 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
  2. Combining CSV Files in Power Query / Get & Analyze (this blog)

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.

Combining CSV Files in Power Query / Get & Analyze

This page shows how to combine all of the CSV files in a folder into a single one - the steps are shown under separate headings below.

Step 1 - Getting a List of the Files

The first thing to do is to get a list of the files:

Getting files from a folder

Choose this option in Excel 2016 (in Excel 2013 it's the same option, but you need to look on the Power Query tab of the ribbon).

Use the Browse button to choose the folder containing your files:

Getting folder

Find and choose the folder containing your files.

Choose to edit the resulting query:

Editing your query

Choose to go into Query Editor.

 

You should now be looking at a list of the files to be combined:

Files to combine

I've included Excel ones and a zipped file in my folder, so I'll need to filter these out.

Now click on the arrow to the right of the Extension column, and choose to show just CSV files:

Choosing CSV files

Exclude any other file types.

Finally (for this step), get rid of columns you don't need:

Removing other columns

Select the columns you want to keep, then right-click on them and choose this option.

Step 2 - Combining the Files

Select the column containing the file contents that you want to combine:

Binary contents

Binary refers to the contents of the file.

 

On the right-hand side of the HOME tab of the ribbon, choose to combine these binaries:

Combine binaries

Providing your files have the same format, it's this easy!

Step 3 - Tidying up the Results

The first thing you now need to do is to tell the Query Editor that the first row of your data contains column headings:

First row

The first row should be promoted to be column headers.

To do this, click on this tool on the ribbon:

Use first row as headers

Choose to use your first row of data as column headers.

You now need to filter out all of the other column headers - here's one way to do this:

Filtering other column headers

Click on the arrow to the right of (say) the ProductId column, and choose to omit all rows where there's a text column heading, not a number.

Almost there!  The Query Editor hasn't adjusted to the new data, and still thinks your columns contain text:

Id columns contain text

You need to change the number columns to contain ... numbers.

Select each of the id number columns, and choose this option:

Id whole numbers

Turn the text entries into whole numbers.  You'll need to do this for the ProductId, HabitatId, Legs and FamilyId columns.

Step 4 - Viewing the Results

You can now load the results into Excel or PowerPivot:

Loading data

Choose to load your data into Excel.

 

The default options will load the data into Excel:

Loading into Excel

Choose these options to load your data into Excel.

Yeah!

The final data

The combined rows.

The beauty of this is that you can now refresh this query at any time, and it will import and combine data from all of the CSV files in the folder you specified.

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