ABC of Power BI: A is for append

Power Query provides a very simple way to append two or more tables into a single “long” table that has all of the rows (and columns) of the input tables. As an accountant, you will find this useful for multi-company reporting.

The multi-company data model problem

Let’s say that you have a simple single company data model for P&L reporting that contains three tables: Dates, NominalRecords and Transactions. It may look something like this:

Now, let’s imagine that instead of just reporting for one company, you would like to report on two: ABC and XYZ. For simplicity, let’s assume that they both share an identical chart of accounts and financial year. We can use the same Dates and NominalRecords tables, but to start out we probably have a separate transactions table from each company. We might be tempted with a model like this:

Now this could work to a certain degree, but it is problematic for two reasons. Firstly, any generic P&L measure that you create to sum the P&L transactions will need to sum the amounts in both transactions tables.

This is a little more complex to write and maintain. It is also (I suspect) a lot less efficient for the underlying Vertipaq engine to scan multiple transactions tables instead of one, to produce a single calculation. Your P&L measure might look something like this:

PAndL =:

Var ABC =

CALCULATE (

      SUM ( TransactionsABC[NetAmount] ) * -1,

      NominalRecords[IsProftAndLoss]

)

Var XYZ =

CALCULATE (

      SUM ( TransactionsXYZ[NetAmount] ) * -1,

      NominalRecords[IsProftAndLoss]

)

Return

ABC + XYZ

I am sure that you can see this becomes more and more unwieldy as you add companies. Secondly, you will not be able directly to use the fields from the transactions tables in visualisations – unless you specifically just want to use a field from a particular company.

A multi-company transactions table

Ideally what we want is a single, multi-company Transactions table, plus a Companies table, so that we can slice by company. The model might look something like this:

In this example, I have added a Companies table that contains details of each company. In the Transactions table I have all transactions from both companies and I have added a Transactions[CompanyKey] column, so that I can create the relationship with the Companies table.

With this structure, my generic profit & loss measure might look something like this:

PAndL =:

CALCULATE (

      SUM ( Transactions[NetAmount] ) * -1,

      NominalRecords[IsProftAndLoss]

)

Notice that in the measure formula we do not need to specify the company or companies. The value Transactions[CompanyKey] is simply an attribute of each transaction line, just like Transactions[Date] or Transactions[Account]. You can of course create a measure to calculate explicitly the P&L amount for a particular company, but generally this won’t be necessary.

Having added the multi-company Transactions and Companies tables, then we can create a multi-company P&L report like this:

Steps to achieve this

You can convert a P&L report from single company to multi-company by following these steps:

1. Add a new table Companies with (at least) company name and a company ID or Key field.
2. Add a new Query(s) to load the Transactions table(s) from the additional company(s).
3. In Query Editor, add a new column to each of the Transactions tables with the CompanyKey.
4. In Query Editor, append the Transactions tables from each company into a single table.
5. In Query Editor, disable “Load” for the source Transactions tables that you have appended to the multi-company Transactions table. This doesn’t stop these tables from refreshing when you refresh your model, but it does stop them from appearing in your Dataset. This makes your model easier to use and also reduces its memory footprint when you open it.

Now, explaining how to do this step by step in a written document is not ideal, so I have prepared a five-minute video for you.

For another day

Now this all sounds very simple, and it is. I have used it to illustrate how you might use the “Append” functionality in Query Editor. I would be disingenuous to suggest that a multi-company report is always this straightforward. What I have not discussed and I will have to leave for another day is how to handle:

• Companies that don’t have the same financial year
• Charts of accounts that are different for each company
• Relationships with multiple paths:
  • Companies > Transactions
  • Companies > Customers > Transactions
• Appending tables that don’t share the same structure

Also not mentioned is that strictly speaking there is no “Append” function as such in Power BI, rather a button labelled “Append Queries” in Query Editor. This takes you through a wizard to define how you want to append two or more queries, but behind the scenes, the actual function used is an M function called Table.Combine. You don’t really need to know this unless you want to write or edit the M code directly.

Summary

It is easy to learn how to append tables in Power BI, and it is a very useful technique to add to your skills if you ever need to create multi-company reports. Just don’t forget to turn off the “Enable load” option in Query Editor for your “appendee” tables.