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.

We created two variants of this report. One uses the GroupBy function to collapse the audit trail. The other uses an algorithm to collapse it. We recommend downloading the GroupBy variant.

Download the Transactions Report for Xero (Filtered using GroupBy)
Download the Transactions Report for Xero (Filtered using Algorithm)

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. Two variations of this report exists.

The reason for these 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.

These variants identify and remove these entries.

One uses the GroupBy function in power query to sum the amounts and remove those summing to 0.

The other, performs complex manipulations to this table to exclude edited transactions and reversal entries.

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-11-05:

  • Enhanced to include GroupBy variation to simplify removing audit trail entries.

2025-05-05: