PivotTable from Many CSV Files

In this post, we’ll summarize data from multiple CSV files with a PivotTable. Specifically, we’ll use a Get & Transform query (Power Query) to retrieve and prepare data from numerous CSV files. Then, we’ll send the query results into the data model (Power Pivot). Then, we’ll build our summary report using a PivotTable based on the data model. And, it will be easy! And fun 🙂

Objective

Before we get too far, let’s just confirm our goal. We have 3 CSV files exported from our accounting system, one for Jan, Feb, and Mar. Here is a sample:

Note that the extract has account numbers, but no account names. And, there are dept numbers but no names. Our summary report needs to display the account and department names, as shown below:

I’ve created a video and full written narrative with detailed steps below.

Now, let’s get er done.

Video

Detailed Steps

We’ll prepare our report using these steps:

• Retrieve with a Get & Transform Query
• Relate Tables in the Data Model
• Summarize with a PivotTable

We’ll walk through each step one by one.

Note: the steps below were performed with Excel 2016 for Windows and uses features that are not available in all versions of Excel, namely, Get & Transform queries (Power Query) and the data model (Power Pivot). In addition, the navigation and dialog boxes may differ from the version of Excel you may be using right now. Please visit the Microsoft website for more information about these features, how to enable them, and which versions include them.

Retrieve with a Get & Transform Query

First, we need to pull the data from all the CSV files. To do so, we select the Data > Get Data > From File > From Folder command. We browse to the folder that contains the CSV files, and click OK. Excel displays a list of files found in the folder, and since we want to import all of them, we just click the Combine button and select Combine & Load To from the dialog shown below.

Note: if we wanted to exclude some files, we would click Edit and then apply a filter to remove other file types (such as PDF).

In the resulting Combine Files dialog, we just click OK.

Then, we tell Excel that we want to add the data to the data model and create a connection only, as shown below.

Click OK, and now we have the data from the various CSV files loaded into the data model. Nice!

Relate Tables in the Data Model

Now it is time to bring in the account names and department names. We have the chart of accounts stored in an Excel table, as shown below.

To add this to the data model, we select any cell in the Table and select Power Pivot > Add to Data Model.

Note: if the chart of accounts is stored somewhere else, such as a database or csv file, we would use the Power Pivot window and use the corresponding Get External Data command.

Then, we repeat these steps to load the Departments Table into the data model.

To tell Excel how these tables are related, we need to define the relationships. We do so by clicking the Power Pivot > Manage command. In the Power Pivot window, we click Home > Diagram View. We can see our three tables, and they have no relationships defined, as shown below.

To define the relationships, we just click-and-drag the AcctID field from the DataFiles table to the related AcctID field in the Accounts table. Then, we drag-and-drop the DeptID field from the DataFiles table to the corresponding DeptID field in the Departments table. Now, the tables are related, as shown below.

Now, all we need to do is summarize the data with a PivotTable.

Summarize with a PivotTable

In Excel, we click the Insert > PivotTable command. We ensure that the Use this workbook’s Data Model is selected, as shown below, and click OK.

Now, we are free to build our report in any desired structure or shape.

For example, we can insert the DeptName field from the Departments table into the Rows layout area. Then, insert the AcctName field from the Accounts table into the Rows layout area. And finally, insert the Amount field from the DataFiles table into the Values area.

Our basic report is done, and we can apply any cosmetics we’d like. For example, we could use the PivotTable Tools > Report Layout > Tabular command and the Subtotals > Show All Subtotals at Bottom of Group to create the report shown below.

And, we did it! And look, all we did was click a few commands … we didn’t need to write a single formula … wow!

Oh yeah, and the best part is that next month, we can simply add the April extract to the data folder, and click Data > Refresh All to update our report 🙂

If you’d like to practice, I’ve provided the sample files below.

Note: Be sure to unzip them before you get started.

• Sample files: PQ2PP.zip