Treasure Maps 3

This is the third post in the Treasure Maps series where we are talking about various ways to implement mapping tables. In this series, we are using mapping tables to help translate labels between the data and the report. In the first post, we used SUMIFS to pull the values into the mapping table. In the second post, we used Power Query. Now, we’ll use Power Pivot. Let’s get to it.

Objective

We have a bunch of data stored in a CSV file, like this:

We’d like to use the data to build a report that looks like this:

Since the labels between the data (1000, 1001, 1002, …) and the report (Cash and Cash Equivalents, …) are different, we create a mapping table that includes the report lines and groups we will need, like this:

Let’s see how we can use Power Pivot to connect the dots.

Video

Narrative

We’ll tackle this in the following steps:

• Retrieve tables
• Relate tables
• PivotTable report

Let’s get to it.

Retrieve tables

Once again we’ll use Power Query to retrieve the tables, but this time, we’ll load them to the data model.

Rather than rehash the detailed Power Query steps in this post, I’ll just provide a quick summary. Feel free to check out the previous post if you’d like to reference the detailed steps.

Just like before, we use the Data > Get Data > From File > From Text/CSV command and browse to the data file. But, unlike before, when we Load To a connection-only query, we need to check the Add this data to the Data Model checkbox, like this:

Checking the checkbox enables us to relate the data table to the mapping table in Power Pivot, which we’ll do shortly. But, before we do, we need to grab the mapping table.

So, again, we use Power Query to retrieve the mapping table and we Load To a connection-only query and again check the Add this data to the Data Model checkbox.

Now that we have retrieved the data and map, it is time to relate them using Power Pivot.

Relate tables

Now it is time to relate the data and mapping tables. That is, we need to tell Excel how the two tables are related to each other. Once again, our tables are related via the AcctID columns.

If you haven’t used Power Pivot before, you’ll want to click the manage data model command on the Data tab … which looks like this:

The first time you click this button, you’ll be promted to enable Power Pivot. Once you do, you should have the Power Pivot tab at the top of your Excel window.

Going forward, you can open Power Pivot by clicking that command on the Data tab, or you can click the Manage command on the Power Pivot tab which looks like this:

With the Power Pivot window open, we click the Home > Diagram View command, and we can then see the data and map tables:

To define the relationship, we simply click and drag the AcctID column from the data table to the AcctID column in the map. The relationship is illustrated like this:

Now that we have defined the relationship, we are ready to create our summary report. We can close Power Pivot and head back to Excel.

PivotTable Report

In Excel, we use the Insert > PivotTable command to open the Create PivotTable dialog:

For the data source, we will Use this workbook’s Data Model. We can place the PivotTable on a New or Existing sheet as desired.

We notice that we have the data and map tables listed in our PivotTable Fields pane. When we expand both tables, we see the columns, like this:

We check the Sub, FS Line, and Amount fields (in that order), and our PivotTable looks a bit like this:

Let’s modify the report layout to only display Assets. (Later, we can create another PivotTable to show Liabilities & Equity.)

Let’s start by filtering out Equity and Liabilities. There are several ways to accomplish this … one way is to right-click each and select Filter > Hide. So, right-click Equity, Filter > Hide. Right-click Liabilities, Filter > Hide. Our updated report looks like this:

Next, we can click and drag the FS Labels to order them as desired:

Next, we turn on subtotals by right-clicking the Current cell and selecting Subtotal from the shortcut menu. Our updated report looks like this:

With our basic report structure looking pretty good, it is all a matter of cosmetics at this point.

We can use the PivotTable Styles gallery to pick a style we like:

We can insert a blank row between groups by clicking the PivotTable Design > Blank Rows > Insert Blank Line After Each Item command:

Toggle off the +/- Buttons and the Field Headers:

We can change the number formats by right-clicking any value and selecting Number Format:

We can enter new labels for the subtotal or header cells if desired:

We can then do the same thing to create the Liabilities & Equity side of the report:

Yay … I think we got it!

Conclusion

The nice thing about this approach is that PivotTables are dynamic … so if there are new assets in the mapping table next period, the report will automatically expand to display them when the report is refreshed.

Although this series has focused on one illustration for mapping tables (a financial statement), please note that this same technique can be applied in other situations when the labels are different between two tables. For example, you download the banking activity and need to get the activity descriptions mapped to accounts. Or, you have an Expensify download that needs to be mapped to expense codes. Or, you have inventory skus that need to be mapped to product codes in your accounting system. And so on. Whenever labels are different between two places, mapping tables can be an efficient way to get from point A to point B.

I hope this Treasure Maps series has been helpful … and I hope mapping tables help you discover the treasure of efficiency in your work 🙂

Sample File