An exciting time for Excel users – the added dimensions of deferred income

Excel now includes a feature that could revolutionise the way finance departments work and dramatically improve productivity. As well as providing a way to speed-up and automate tasks that spreadsheets already carry out, this feature will allow Excel to extend its reach into whole new areas. If you haven't guessed already, this new feature is the Power Query Add-in for Excel 2010 and 2013 which is now built in to Excel 2016 as the Get & Transform group of the Data ribbon. It’s easy to assume that Power Query is just another part of the relentless barrage of data analysis tools that Microsoft persist in bombarding us with, not least because of the way in which Microsoft itself describes the Power Query Add-in:

"Power Query enhances self-service business intelligence (BI) for Excel with an intuitive and consistent experience for discovering, combining, and refining data across a wide variety of sources including relational, structured and semi-structured, OData, Web, Hadoop, Azure Marketplace, and more. Power Query also provides you with the ability to search for public data from sources such as Wikipedia."

Power Query is much more significant than just a method of working with external data. Its real power comes from its ability to add new dimensions to the way you can work with spreadsheet data. The use of the word dimensions is not accidental. One of the great drawbacks of spreadsheets is the limitations imposed by a two-dimensional grid of cells. The information we need to work with doesn't always fit neatly into a flat list of rows and columns. For example, if you think about a list of clients, each of those clients could be responsible for a number of orders, and each of those orders could contain a number of individual order lines – it's as though a cell on a row 'contains' a separate table of data with its own rows and columns Not only that, this structure of Tables within rows can cascade down to multiple levels. Although Excel Tables introduced in Excel 2007 do make it more possible to reproduce this sort of structure in a spreadsheet, it is likely that re-assembling the data for reporting is likely to require some relatively complicated conditional sum and lookup/match formulae.

Things get even more complicated when we are not just working with existing tables of data but also need to generate additional dimensions. To do this in a fully automated way becomes close to impossible due to the need to cope automatically with varying numbers of items in the various tables.

As well as giving Excel the capability of working with external data, Power Query allows Excel to manipulate data within an Excel workbook using database techniques and thus allows problems that require these additional dimensions to be tackled.

As a practical example, I was lecturing on Power Query and other recent Excel changes and, at the coffee break, one of the delegates asked whether Power Query might be the solution to a problem that they were struggling with. Each month, they needed to allocate subscription receipts to the future months that those receipts covered. Each month there were thousands of transactions that had to be matched against a database of tens of thousands of clients to find each client's contract terms and contract end date. This information was then used to allocate the received amounts against a separate column for each future month. Because of the complexity of the formulae involved and the large number of exact VLOOKUP() formulae required, the spreadsheet was almost unusably slow, and the end result unwieldy to work with.

Using Power Query to replace thousands of VLOOKUP() formulae with a simple query join is relatively straightforward. As long as you have fields in the two separate tables that link one to the other you can just load each Excel Table and save it as a 'Connection only'. This will then allow you to use New Query, Combine Queries to merge the two queries using the linking field to join them. Once merged, you can produce a single output query or table including the required information from both of the two original tables.

The more difficult task in this case was to create the output in a format that would allow for easy processing and reporting. Rather than additional columns for each period's data, it would be better to have a single amount column but separate rows for each period. Assuming annual subscriptions, this would require each transaction to be transformed into up to 13 rows. Each row would start off being identical apart from containing different period end dates. If there's an easy way to do this automatically in Excel rows and columns, I haven't yet worked out what it is. In the Power Query editor we can just add a column to our existing, merged, table that includes a list of the numbers 0 to 12. The interface would help us to do this using the Add Column, Add Custom Column command but we would need to know about the existence of the List.Numbers() function:

Each of our transaction rows now includes a 'cell' in the new custom column that holds this additional 'dimension'. If we expand the contents of this cell we create our additional rows:

We can then use further added columns to use our numbers to create 13 month end dates and then allocate the appropriate proportion of each of our receipts to the relevant month or part of a month.

Our finalised query can then be loaded to a worksheet table and used to produce whatever reports we require, such as a list of accrued amounts by period.

Once set up, if our transactions and clients tables are linked directly to our accounting system, each month we can just refresh the tables and our queries using Data, Refresh All command to load the current month's data into our results table.

If you are interested in a more detailed guide, as well as a range of blog posts on different Excel issues, the Excel Community also includes some extended articles that cover certain subjects in more depth. The most recent of these presents this project as a case study with a step-by-step approach to the key stages of the process. See below for more details about the Excel Community and how to join.

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.