Four years ago, the Excel Community featured a series of articles on using Power Query to fully automate the consolidation of multiple lists of account balances, summarise them according to a nominal code structure table and, finally, turn the resulting output table into a set of formatted accounts (see the archived web page).

For this two-part series, we are going to concentrate on the production of the formatted accounts stage. So, instead of starting with several separate workbooks containing lists of nominal codes and balances, we will start with a single list (see Example 1).

We’ll use a nominal CodeChart Table on a different worksheet in the same workbook to present our figures.

We will be using slightly different Power Query methods here than we did in the Excel Community articles. This highlights the versatility of Power Query, but it also shows that complex results can be achieved using only the Power Query interface options and without the need to enter any functions manually. We are also assuming a basic knowledge of how the Power Query interface works.

Example 2 shows the result that we are seeking to achieve.

CodeChart

Example 3 shows our CodeChart Table. The Table has three main functions. First, the Code and Category columns are used to summarise our individual codes, according to the hierarchy set out. So, for example, any rows with a code greater than or equal to 100, but less than 200, will be included in the Cost of sales row.

Second, we will use the CodeChart Table to set the format for each of our output rows. The Column column defines which of our two final accounts columns each summary total should be displayed in. The DrCr column defines the natural sign of each value so that values of the opposite sign can be formatted accordingly.

Finally, the SubTotal column defines which rows are to be calculated by summing other rows, with the First and last columns defining the range of codes that should be totalled for each total row. Note that the assumption is that our original list of balances can only include integer values in the Code column.

Summary by Category

We are going to approach the Power Query element of our task in two main stages. First, we will summarise our individual balances according to our CodeChart. The resulting Table will not only provide the category rows for our final accounts, but it will also be the basis for the second stage: creating our total rows and combining them with the category rows to create our final output table.

There are several different methods that we could use to create our summary. In the original series, we used the ability to merge queries, but here we are going to append our trial balance Table with our CodeChart Table. As we can see from the screenshots, the only column common to both Tables is the Code column. We load both of our Tables into the Power Query editor and load them as Connection Only queries. This makes them available in the editor without creating duplicate Tables in our workbook. We can then use the Data Ribbon tab, Get & Transform Data group, Get Data dropdown to choose Combine Queries, and then choose Append. This concatenates all the rows from our two Tables with only the Code column including items from both Tables (see Example 4).

We need to allocate the CodeChart information to the TB rows. At the moment, we are not interested in our SubTotal rows, so we will start by filtering our SubTotal column to only include rows where the value in the column is null. Then, we will sort our table in ascending order, first by the Code column and then by the Amount column. This should result in the rows from the CodeChart being at the top of each category or our codes. We can then select our Category, Column and DrCr columns, right-click on the header of any of our three selected columns and choose Fill, Down (see Example 5).

This copies the Category, Column and DrCr values from the top row of each category to all the other rows before the next category starts (see Example 6).

Once we have applied these values to our individual rows, we can filter out any rows where the Amount column has a value of null in order to remove the rows that originally came from our CodeChart Table.

The final step of this stage is to group all of our individual rows by category using the Transform Ribbon tab, Table Group, Group By option. Because we need to include multiple group and aggregation columns, we choose the Advanced option and include Category, Column and DrCr as groups, and Code and Amount as aggregates. We need to preserve a code value for each row to be able to sort our rows into the right order for our final accounts and create our totals, but it doesn’t matter which of the codes we use, so we have set the Operation for the Code column to Min (see Example 7).

We now have all of our category rows with all of the columns we need to create our totals and include in our final accounts table. We’ll cover this stage in the next edition of Chartech.