Transaction Reports for Xero in Power BI

Home / Report Templates / Transactions Reports for Xero in Power BI

The Transactions Reports for Xero in Power BI is a simple report that allows you to review and analyse your General Ledger (GL) data posted to Xero. For an Excel version of this report, see the Excel Transactions Reports for 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 It also includes additional fields from the transactions and contacts endpoints.

  • Transaction Reports for Xero in Power BI
    Transaction Reports for Xero in Power BI

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 uses the Matrix visual element, allowing you to easily drag, drop and/or remove columns as needed.

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

We provide 2 versions of this dashboard. Once downloaded, you can connect it to your own data using this article.

Transactions Report for Xero

This version of the report uses and includes all journals posted to Xero to create the data set.

Download the Transactions Report for Xero

Transactions Report for Xero (Filtered)

This version of the report filters journals posted to Xero and keeps only the final journal for a transaction. Journals are an audit trail that includes multiple records each time a transaction is edited or deleted. You can learn more about the algorithm we devised to filter out these records.

Download the Transactions Report for Xero (Filtered)

How to Refresh in Power BI Desktop

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

  1. Under the Home tab, go to Transform Data and click on Edit Parameters
  2. Enter the Start Date and End Date 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. (optional) If using the Tracking Categories version of the report, enter the name of your Tracking Category 1 and 2.
  4. Click the Refresh button and wait for the data to download.

Understanding the Power Queries used to produce the numbers

The Transactions Report for Xero (Filtered) 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 version of the 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.

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

2025-05-05: