Guest Post by: Mike Griffin

Intro by Avi: Power Pivot and Power BI tools can be used to transform BI for a wide array of industry verticals and vocations. But it is especially suitable to some roles; Accountants are probably at the top of that list! Our friend Mike here is a Financial Manager in the interesting vertical of Cruise Lines. And has a post for us describing just one of the ways they are using the Power BI tools, in this case to find needles in the haystack. Take it away Mike…

Accountants are NOT typically data GEEKS

Accounting related problems open doors to a different set of applications for PowerPivot and PowerQuery. Although it’s fair to say most accountants like numbers, an affinity for numbers does not always translate into a love for data – especially lots of data. This example illustrates how PowerPivot and PowerQuery can be used to help automate accounting related tasks that can be incredibly time consuming when a lot of data is involved.

The scenario I’m presenting is not sophisticated in terms of DAX formulas and is very simple from a data modeling point of view. However, it’s an incredibly useful application of the tools we use as PowerPivot enthusiasts that can save valuable time when closing the accounting period.

The Accounting Need: Remove needles from the hay stack

Use Power Pivot and Power BI to look for the proverbial needles in your data haystack
Use Power Pivot and Power BI to look for the proverbial needles in your data haystack

In this scenario, I need to reverse invoice specific journal entries that were originally posted as part of an automated process between an internal database and our accounting software. This entry is posted as a batch with thousands of other invoices (the original journal entry can’t just be reversed).

A data source is available with details at an invoice level that represent the batch total. However, this data source does not share the same names for the account details required to reverse the original journal entry and it is not data-model friendly.

The Goal:

  • · Extract 17 of the 118 columns available in the detailed data source with the invoice level detail.
  • · Map the 17 columns to the appropriate general ledger accounts in another table and add other details for the journal entry.
  • · Isolate a subset of the invoice level details based on another table with the specific invoices I need.

I’m working with three different tables:

  1. EntryAccounts” table that translates the column labels in my detailed data set into the appropriate journal entry details such as account number, department, etc. (see Step One below)
  2. BookingList” table that helps me extract the specific invoices I’m interested in from the detailed list of thousands. (see Step One below)
  3. RevDetail” table which is a detailed data set with all of the invoice specific information I need (the transformed version of this table is in Step Two below).

Step One:

Create a table that maps the appropriate columns from the data source to the general ledger accounts I want and a list of the specific invoices I want to isolate.

  • · Load each table into PowerPivot
Table One: EntryAccounts   Table Two: BookingList
Accounting Detail for PowerPivot   Invoice List
The Rev_Description column is a list of the column headers I’m interested in from the detailed data source with invoice specific details.   The Invoice List table helps filter the list of thousands of invoices in my large detailed data set to the smaller subset I’m interested in.

Step Two:

I use PowerQuery to turn the detailed data source table with roughly six thousand rows and 118 columns into a three column data-model friendly format. I’ve “un-pivoted” the columns so I can create a relationship between what had been column headers to the unique row values in the Rev_Description column in the “Entry Accounts” table. Note: this step turns the table into over 85K rows in this case. (A tall and skinny table – lots of rows, fewer columns – is far more ideal shape for Power Pivot than short and squat).

This is the PowerQuery view of what becomes the “RevDetail” table in PowerPivot.

Table Three: RevDetail  
Unpivot data with PowerQuery Steps to unpivot with PowerQuery
  • · Load the table into PowerQuery, un-pivot the data, & filter the attribute column for the columns I’m interested in. These columns are synonymous with specific account numbers on my “EntryAccounts” table.
  • · Remove unwanted columns and load this table into PowerPivot if using Excel 2010 (the query can be loaded directly into the PowerPivot data model in Excel 2013 – a very useful feature!)

Step Three:

Relate the invoice level data to the subset of invoices & appropriate accounting details.

Create Relationship - revised

  1. The Attribute column in the “RevDetail” table is related to the “Rev_Description” column in my “Entry Accounts” table. The Attribute column had been 17 different columns in the original data source.
  2. The Invoice column in the “RevDetail” table is related to the Booking column of my “Invoice List” table.

Step Four:

Create a pivot table that brings the data source, specific invoices of interest, and mapped accounting details “in line” for review and ready to scan with some VBA.

Flag data with PowerPivot Related

Create calculated columns to create filters for the pivot table output.

PowerPivot Output

I’ve pulled the appropriate details into a pivot table. The relationship between the RevDetail table and the BookingList table help me filter down to the invoices I care about. The relationship between the RevDetails table and the EntryAccounts table allow me to pull in the accounting related detail that is not in my detailed data source. From here, I use a little VBA that scans the pivot table and moves the data into a template that is designed to interface and upload with the specific accounting software being used.

Journal Entry with VBA and PowerPivot

The Result:

This process takes no more than ten minutes to gather and upload all required data, leading to a journal entry that can be uploaded to an accounting system. Creating these entries had taken 15 to 20 minutes per invoice prior to implementing PowerPivot as a tool to automate this process. This example was only isolating four invoices; the list is often longer. The time savings can be huge!