Excel Transactions Reports for Xero

Home / Report Templates / Excel Transactions Reports for Xero

The Excel Transactions Reports for Xero is a simple report that allows you to review and analyse your General Ledger (GL) data posted to Xero.

This report can also consolidate data from multiple Xero organisations (data files) when using the Consolidation OData Feed.

It uses the JournalsAccrualTable endpoint to download all debit/credit amounts posted to your GL and performs complex manipulations to this table to exclude edited transactions and reversal entries. You can read more about this at the end of the article.

  • Excel Transaction Reports for Xero
    Excel Transaction Reports for Xero
  • Excel Transaction Reports for Xero - Data Set
    Excel Transaction Reports for Xero – Data Set

While this is a fully working and standalone dashboard, it can also be used as a starting point that can be further customised or modified as required to fit whatever business rules you need. It is not locked in any way. Because of this, it can also be used to learn how to create power queries, how to create DAX formulas, etc.

Reports Included

Transactions Report

This report mimics the Account Transactions Report or General Ledger Detail Report within Xero and list all transaction amounts posted to your General Ledger (GL).

It is provided as a pivot table allowing you to easily drag, drop and/or remove columns as needed.

Data Sheet

The data sheet provides a table of data as rows and columns you can use as a stand alone data sheet.

Requirements

To use the report, you will need to:

  1. Have an OdataLink Subscription or Trial Account.
  2. Connect OdataLink to your MYOB company file.
  3. Configure a Model that includes the following endpoints:

Download and Setup

You can download the Excel file from the following link.

Once downloaded, you can connect it to your own data using this article.

How to Refresh in Power BI Desktop

To refresh it in Power BI Desktop, follow these steps.

  1. Go to the Inputs sheet to enter the Parameters driving this report.
  2. Enter the Date Start and Date End for the months you wish to report on.
    This can be any date range but the larger it is, the longer the refresh will take to run.
  3. Choose whether to include the following data.
    • Invoices
    • Credit Notes
    • Payments
    • Prepayments
    • Overpayments
    • Batch Payments
    • Bank Transactions
    • Bank Transfers
    • Manual Journals
      Note that this will slow down the refresh.
  4. Select whether to Include Contacts Data or not.
    Including contacts data will slow down the refresh but allow you to see the contact groups information.
  5. Go to the Data tab in the ribbon, click the Refresh All button and wait for the data to download.
  6. Select a Worksheet and use the Filters to display the data you want.

Understanding the Power Queries used to produce the numbers

As stated previously, the report uses the JournalsAccrualTable endpoint to download all debit/credit amounts posted to your GL. It then performs complex manipulations to this table to exclude edited transactions and reversal entries.

The reason for these complex manipulations stems from the way the journals endpoint operate. Xero designed the journals endpoint to provide an audit trail of all changes posted to your GL. When a transaction is edited, Xero post reversal entries of the original transaction and re-entries of the current state of the transaction. Similar concepts applied to deleted or voided transactions.

While this mechanism is useful for auditing purposes, it does create confusion when looking at it from the transactions point of view.

The Power Queries in this report performs complex operation to remove these duplicates. This ensures that a single set of numbers is displayed for a given transaction as opposed to a complete audit trail of all debits/credits ever made.

It also showscases an approach that can be used to optionally load additional data (such as data coming from the Invoices and ContactsExpanded endpoints)

Find an issue with the template?

Let us know of any issues you find with the template. We built it to use the least amount of data possible but this might not suit everyone.

Change Logs

2024-10-10:

  • Relationships were broken on the template.
  • There was a performance issue where the buffering was done too early and causing all queries to run.
  • Credit notes were being unfiltered correctly due to rounding and very small number which should have rounded to 0. Applied rounding to resolve this issue.