ICAEW.com works better with JavaScript enabled.

Excel Tip of the Week

Excel Tip of the Week #385 - Power Query: Creating transaction references

Author: David Lyford-Smith

Published: 16 Mar 2021

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

Hello all and welcome back to the Excel Tip of the Week! This week, we have a Creator level post in which we are looking at how to use Power Query to generate a couple of common kinds of transaction reference easily and automatically – including one that’s incredibly hard to do with just formulas alone.

Transaction numbers by customer

First, we will look at a situation such as this:

Excel screenshot

Here we have invoices for a selection of customers, and want to assign each a sequential number based on the customer. Of course this approach also works for numbering within each date, or via some other field – we’re just using customer name as an example.

We start by loading our data into Power Query with Data => From Table/Range. This will let us work with the data. Our first step is to group the table by the Customer column, selecting ‘All Rows’ as our grouping type:

Excel screenshot
Excel screenshot
The output contains all the original data, but compressed into four subtables, one for each customer. We are then going to add an index column to each of these subtables, by using the M language to add a custom column:
Excel screenshot
This function is based on the one automatically created by using Add Column => Index Column. It will add this new column into each of the invisible subtables, creating a new set of subtables:
Excel screenshot
We can now proceed to delete the unnecessary columns and expand out our new column:
Excel screenshot
Note that the index column restarts once the second customer is reached. Now all that remains is to create our column. For this the easiest method is to use “Column From Examples”:
Excel screenshot

Here we just write the first number and Excel’s AI does its best to predict the rest. We can see that the 10th B invoice is “B-0010” rather than “B-010”, but if we correct that one manually Excel will correctly figure out what we want, which is this M code:

=Table.AddColumn(#"Removed Columns", "Custom", each Text.Combine({[Customer.1], "-", Text.PadStart(Text.From([Index], "en-GB"), 3, "0")}), type text)

Then we can just tidy up our column names, remove duplicates, and export:

Excel screenshot

Creating references from a summary

The above example is possible with Excel formulas, but now we are going to look at something that is beyond what’s practical with formulas alone – creating a full numbered listing from a summary.

Here’s the data we’re working with this time:

Excel screenshot
All we have for each date is a number of transactions. We want to expand this out into a list of individually numbered transactions in the format mm-dd-001. Let’s bring this into Power Query and get started. This time, our first step is to create a new subtable for each date, with a number of rows equal to the number of transactions. This is done by creating a new custom column:
Excel screenshot
This uses the Table.Repeat command to make a repeated number of copies of a simple table. The table we are repeating is created directly from manual records with the Table.FromRecords command. We then specify that the table is just a single new column with a placeholder value of 1, and that the number of times to repeat is the number in the “Number of transactions” field (using the # identifier). This creates our new subtable column:
Excel screenshot
From here we can more or less follow the same procedure as before – adding an index column to each subtable, expanding out, and then amending from there. Here’s our final product:
Excel screenshot

You can see both sets of data, both queries, and the final results in this file

You may also like

Excel community

This article is brought to you by the Excel Community where you can find additional 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.

Excel polaroid
Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.