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

Transaction numbers by customer

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

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:

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:

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:

We can now proceed to delete the unnecessary columns and expand out our new column:

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”:

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:

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:

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:

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:

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:

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

You may also like

• Previous post - Data types
• TOTW index
• Next time – Power Pivot – RELATEDTABLE