{"id":3801,"date":"2020-05-26T15:17:19","date_gmt":"2020-05-26T15:17:19","guid":{"rendered":"https:\/\/sloane.co.uk\/finance\/blog-post\/financial-reporting-blog-post\/abc-of-power-bi-a-is-for-append\/"},"modified":"2020-06-10T15:47:06","modified_gmt":"2020-06-10T15:47:06","slug":"abc-of-power-bi-a-is-for-append","status":"publish","type":"post","link":"https:\/\/sloane.co.uk\/finance\/blog-post\/financial-reporting-blog-post\/financial-reporting-financial-reporting-blog-post\/abc-of-power-bi-a-is-for-append\/","title":{"rendered":"ABC of Power BI: A is for append"},"content":{"rendered":"

ABC of Power BI: A is for append<\/p>\n<\/p>\n

\n

Power Query provides a very simple way to append two or more tables into a single \u201clong\u201d 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.<\/p>\n

The multi-company data model problem<\/h3>\n

Let\u2019s 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:\"Append\"<\/p>\n

Now, let\u2019s imagine that instead of just reporting for one company, you would like to report on two: ABC and XYZ. For simplicity, let\u2019s 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:
\"Transactions<\/p>\n

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.<\/p>\n

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:<\/p>\n

PAndL =:<\/p>\n

Var ABC =<\/p>\n

CALCULATE (<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0 SUM ( TransactionsABC[NetAmount] ) * -1,<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0 NominalRecords[IsProftAndLoss]<\/p>\n

)<\/p>\n

Var XYZ =<\/p>\n

CALCULATE (<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0 SUM ( TransactionsXYZ[NetAmount] ) * -1,<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0 NominalRecords[IsProftAndLoss]<\/p>\n

)<\/p>\n

Return<\/p>\n

ABC + XYZ<\/p>\n

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 \u2013 unless you specifically just want to use a field from a particular company.<\/p>\n

A multi-company transactions table<\/h3>\n

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:<\/p>\n

\"Multi-company<\/p>\n

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.<\/p>\n

With this structure, my generic profit & loss measure might look something like this:<\/p>\n

PAndL =:<\/p>\n

CALCULATE (<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0 SUM ( Transactions[NetAmount] ) * -1,<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0 NominalRecords[IsProftAndLoss]<\/p>\n

)<\/p>\n

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\u2019t be necessary.<\/p>\n

Having added the multi-company Transactions and Companies tables, then we can create a multi-company P&L report like this:<\/p>\n

\u00a0\"P&L<\/p>\n

Steps to achieve this<\/h3>\n

You can convert a P&L report from single company to multi-company by following these steps:<\/p>\n

    \n
  1. Add a new table Companies with (at least) company name and a company ID or Key field.<\/li>\n
  2. Add a new Query(s) to load the Transactions table(s) from the additional company(s).<\/li>\n
  3. In Query Editor, add a new column to each of the Transactions tables with the CompanyKey.<\/li>\n
  4. In Query Editor, append the Transactions tables from each company into a single table.<\/li>\n
  5. In Query Editor, disable \u201cLoad\u201d for the source Transactions tables that you have appended to the multi-company Transactions table. This doesn\u2019t 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.<\/li>\n<\/ol>\n

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

    For another day<\/h3>\n

    Now this all sounds very simple, and it is. I have used it to illustrate how you might use the \u201cAppend\u201d 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:<\/p>\n