One of the most common problem when it comes to formatting your Xero data using Power BI or Excel has to do with tracking categories.
This article aims to provide the necessary information and steps required to shape Tracking Categories.
You will learn how to use Power Query (the query engine at the heart of Power BI, Excel and Data Flows) to separate Xero Tracking Categories into distinct columns by using using custom column formulas.
A bit about Xero Tracking Categories
Xero allows you to define up to 2 Tracking Categories. Each Tracking Category can have multiple values called Options.
These values can be can be tagged and assigned to transactions such as Invoices or Journals.
Once assigned, Xero has the ability to run reports targeting one or more tracking category option.
In this way, tracking categories can be used to segment and analyse your business.
Not only that, but this information is also available to third-party tools such as OdataLink. 
Understanding how Xero Tracking Categories appears within Power Bi and Excel using OdataLink
Xero Tracking Categories are available via OdataLink within multiple endpoints. Typically, the Lines or LineItems element of endpoints will contain the Tracking Categories.
You can use the Expand step within Power Query to get to this information. However, doing so will, in most situations, give you the wrong results.
The figure below illustrates this issue.
When expanding the tracking categories of the InvoicesExpanded endpoint, rather than having two columns of data, one for each tracking category, you will end up with a single field with multiple rows of data.
Not only that, all lines containing multiple tracking categories get duplicated.
This is primarily due to how the data is structured within Xero.
Rather than having two distinct fields, tracking categories are returned as a table of information, tagged against each line.
When you expand this data, this information is placed in a single column that repeats down the data set.
When displayed within Power BI or Excel, this single column doesn’t lend itself to being charted easily
The solution is to use Custom Columns to pull the tracking categories into distinct fields. Then, you can use these distinct fields within your dashboard and reports.
Creating the column to pull a specific Xero Tracking Category
The following actions must be done within the Power Query engine. To get to Power Query, right-click on your data set and choose Edit Query.
Before creating the Custom Column, you must remove any Expand step applied to the tracking category element as Power Query may raise an issue.
You must also identify and pay particular attention to the names of the fields. They will be needed within the formula.
To add a custom column, follow these steps.
- Under the Add Column tab in the ribbon, click on the Custom Column button.
- Enter a name for the column.
- Enter a formula using the following syntax.
Table.SelectRows([Table], each [FieldTrackingCategory] = “TrackingCategoryName”){0}[FieldOption] - Right-click on the newly created field and choose Replace Errors…
- Enter null and click OK.
Understanding the syntax of the formula
The following diagram of Power Query /Power BI explain how the syntax of the custom column formula works.
[Table] is the name of the field storing the Xero Tracking Categories.
In the above diagram, the table is called [LineItems.Tracking].
[FieldTrackingCategory] is the name of the field within the Xero Tracking Categories table that provides the name of your tracking category.
In the above diagram, the field is called [Name].
“TrackingCategoryName“ is your Xero Tracking Category name.
In the above diagram, the Xero Tracking Category is named “Region”.
[FieldOption] is the name of the field proving the Xero Tracking Category Option.
In the above diagram, the field is called [Option].
Using the illustration above, the formula should be:
Table.SelectRows([LineItems.Tracking], each [Name] = “Region“){0}[Option]