skip to Main Content

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!

This Post Has 11 Comments
  1. Need more accounting tricks! At the back of my mind I half suspect PowerPivot and Query was dedicated to accountants.
    In my current workplace I processed the entire dump of accounting entries exported from our accounting system into P&L, Balance Sheet, and quite a few other accounting schedules. Also side-loaded budget data via many-to-many relationship, and I have a versatile cube to entertain the neverending barrage of whys. Explaining/Searching for irregularities has never being faster since i could do a live drill-down to the debits and credits which forms the basis of every perceivable number.
    Just wished I could use 64bit Excel, I have being getting the “out of memory” error after a few refresh runs.

    1. Hi en, I’ve done some similar work with respect to creating financial statements using PowerPivot. The time intelligence functions are life savers! It’s nice to hear from other folks out there pushing the boundaries of what’s possible when it comes to financial reporting and variance analysis.

      1. I only discovered Pivot Table after 5 years of building long formula. I remember the days when I would wait 10mins for formula to populate a huge spreadsheet, but still it beats an entire day of manual data entry. Amazed by the speed and elegance that Pivot process data, I am then drawn deeper into the world of cubes via PowerPivot as I refuse to accept the limitation of Pivot seeing how genius it was conceptually. Pivot or PowerPivot is severely underutilized in accounting field.

  2. En, there’s a post on this site titled “Grab Bag”. It has the most awesomest thing ever in the history of ever. There is a script that basically removes the memory limit on 32 bit excel. I tried it. It works flawlessly. You need to be the admin of your computer to get it to work. Hopefully you are.

    1. Hi Mike,

      Thanks for suggestion. Unfortunately there are other add-ins from our accounting software that specified compatibility with 32bit only. So.. until they move on I have to live with it :<

  3. When I used the script that removed the 32 bit memory restrictions, my add ins and odbc drivers still worked flawlessly. This is not converting office to 64 bit. However, I do not know your exact situation so I may be wrong, but this worked so well for me that it’s almost as life changing as powerpivot

  4. I totally agree that PPPQ is a great combo for financial/accounting reporting. I was using a number of GL reporting packages (notably FRx & Renovofyi ) but the former was discontinued by MSFT years ago and the later just didn’t fit what I wanted. My GL vendor suggested Crystal Reports which made me give a mental “ugh”. While I have used it a lot and it is a good transactional reporting tool, Crystal is not a good gl reporting tool. Way too many workarounds to do even relatively simple stuff. Plus from there what was the #1 wish? Export to Excel of course. Anyone who has exported from Crystal knows it usually takes a lot of post cleanup work. Too much to the point you avoid running the report if you can(which ironically defeats the purpose of having it).

    So when I stumbled upon PPPQ and learned what it can do I was finally able to produce the dynamic reports we always needed. Income statement & Balance sheet reports: quarterly, rolling 12, 6 forward-6 back, budget v actual and on and on are so easily doable. Soon I branched out into depreciation, heavy equipment charge rate analysis, inter-company related party eliminations, and lots of payroll stuff (recon’s for 940, 941, unemployment, worker’s comp, etc). Like everyone else we were previously doing things with a lot more of a manual process.

    I get some flak at times with the “oh no, not another spreadsheet” syndrome; but really what is the alternative? My native gl package doesn’t suit our needs so basically it is up to most folks to roll their own. And I can’t think of another set of tools I have used that provide near the level of dynamism and flexibility.

    I agree with Rob that the icing on the cake to me has been the introduction of Power Update. Worth its weight in gold as it removed a big pain in my neck with having to manually update reports. Now it is mostly fire and forget. If there is an issue I get an email and I go fix it. But It has been running for weeks now with no hiccups. So 100% appreciate that tool as well. Perhaps I should amend my acronym to PPPQPU as the stack to use(?).

  5. I am the director of finance at a medium sized company with a very outdated ERP/GL system. For two years, I have used VBA to loop through messy exported income statements and balance sheets and create a data table. The data table feeds an army of pivot tables that feed Income Statements, Balance Sheets and Cash Flow Statements. I have rigged metric calculations all over the pivot tables. It has worked but it is still a bit messy and time consuming to maintain.

    I hope to use Power Pivot to clean up the process.

    Any advice is much appreciated.

Leave a Comment or Question