Power Query: combine monthly shipment files in one click

If you get a new Excel report every month and paste them together by hand, Power Query can do that for you. You set it up once. After that, you drop the new file in the folder and press Refresh.

Before you start

• Put all the monthly files in one folder, and nothing else in that folder.
• The files should have the same column names, in the same sheet name. Small differences cause errors later.
• Power Query is built into Excel 2016 and newer, and Microsoft 365. It is under the Data tab.

Steps

1. Open a new Excel workbook.
2. Go to Data, Get Data, From File, From Folder.
3. Choose your folder and click Open.
4. You will see a list of files. Click Combine, then Combine & Transform Data.
5. Pick the sheet or table to use from the sample file, and click OK.
6. The Power Query editor opens with all files stacked into one table.

Clean it in the editor

Everything you do here is recorded as steps and replayed on every refresh. Useful things to do:

• Remove top rows if each file has a title above the headers (Home, Remove Rows, Remove Top Rows).
• Use first row as headers (Home, Use First Row as Headers).
• Set data types by clicking the small icon in each column header. Choose Date for dates and Decimal Number for amounts.
• Remove blank rows (Home, Remove Rows, Remove Blank Rows).
• Trim text: right-click a text column, choose Transform, then Trim. This fixes extra spaces.

The first column often shows the file name (Source.Name). Keep it. It tells you which file each row came from, which helps when you find a problem.

Load and refresh

Click Close & Load. Your combined table appears in a new sheet. Next month, copy the new file into the folder and click Data, Refresh All. The new rows appear.

If it breaks

• "Column not found" error: a new file has a renamed column. Fix the file or the step that mentions that column.
• Wrong numbers: check the data type of the column. A number read as text will not add up.
• Duplicates: make sure you did not leave an old copy of a file in the folder.

What next?

The same combined table can feed a pivot table (see pivot tables for freight cost) or a Power BI report (see on-time delivery dashboard). Power BI uses the same Power Query tool, so what you learn here carries over.