If you are reporting in Power BI, you will quickly run into a common challenge: your data does not always arrive in the same structure.
For example, in the Xero tracking_categories table, you might see values such as:
- Region – North
- Region – South
- Region – West Coast
In many cases, the tracking category name and the option are stored together in a single field. However, a colleague might send you an Excel file where the same information is split into two columns:
Column A: Region
Column B: North, South, West Coast
If you want to report on both datasets together, the structure needs to match. In this article, we will walk through how to split a combined field into multiple columns, how to merge fields back together, and how to align Xero and Excel data properly in Power BI.
We will use real-world examples relevant to users of Connectorly for Xero & Power BI and Connectorly for HubSpot & Power BI.
Why Do I Need to Split or Merge Fields in Power BI?
This question often comes up when accountants start building consolidated reports.
Power BI relationships depend on consistent keys. If one table contains “Region – North” as a single value, and another table stores “Region” and “North” separately, Power BI cannot match them directly.
To create a relationship or a proper merge, the structures must align. That means either:
- Splitting the combined field into two columns, or
- Merging the two separate columns into one matching field
Both approaches are valid. The right one depends on your data model.
How to Split a Combined Field in Power BI
Let’s assume your Xero data contains a column with values like:
Region – North
To split this into two fields:
- Open Power BI Desktop.
- Go to Transform Data to open Power Query.
- Select the column that contains the combined value.
- Choose Split Column → By Delimiter.
- Use the dash (–) as the delimiter.
This will create two new columns:
- Tracking Option Code.1 that you can rename to Tracking Category
- Tracking Option Code.2 that you can rename to
Tracking Option
You may want to trim spaces afterwards using Transform → Format → Trim.
Now your Xero data structure matches the Excel file structure where “Region” and “North” are already separated.
At this point, you can create a relationship between:
Xero Tracking Option ↔ Excel Option
This ensures your reports combine both datasets correctly.
If your Excel file is stored in SharePoint, you can follow our step-by-step guide here:
This approach is especially useful when building consolidated reports with Connectorly for Xero & Power BI, where financial data needs to align with planning or operational data coming from spreadsheets.
How to Merge Two Fields into One in Power BI
Sometimes you want to go the other way.
If your Xero dataset uses the combined format “Region – North” and you prefer that structure, you can merge the Excel columns instead.
In Power Query:
- Select both the Tracking Category and Tracking Option columns.
- Choose Merge Columns.
- Use “ – ” as the separator.
- Name the new column something consistent, such as Tracking Combined.
Now both tables contain a matching single field. You can create a relationship or use Merge Queries to bring the datasets together.
This technique is also helpful when working with CRM data in Connectorly for HubSpot & Power BI, where fields such as Deal Stage and Pipeline might need to be combined to match financial reporting structures.
Which Approach Is Better: Split or Merge?
In most reporting scenarios, splitting the field is cleaner.
Why?
Because separate columns give you more flexibility. You can filter by Region only, or by Region and Option. Your data model becomes more robust and easier to extend later.
For accountants building P&L reports with tracking categories, having separate columns allows better slicing by cost centre, department, or location.
For sales managers combining HubSpot deal data with financial outcomes, consistent field structures ensure accurate revenue attribution.
The key is consistency across all tables in your model.
How Does This Fit into a Real Connectorly Workflow?
When you use Connectorly for Xero & Power BI, your Xero data is already structured for reporting. However, additional datasets such as budgets, forecasts, or operational spreadsheets may require transformation.
Similarly, with Connectorly for HubSpot & Power BI, you may combine CRM data with financial data to track sales performance against revenue.
Field alignment is often the small technical step that makes the entire reporting model work smoothly.
Taking a few minutes in Power Query to split or merge fields properly will save hours of troubleshooting later.
Conclusion: Make Your Data Structures Work Together
Power BI is powerful, but it expects structured data.
If your Xero tracking categories arrive combined and your Excel data is split, or vice versa, you simply need to align them before building relationships.
Splitting columns by a delimiter and merging columns are straightforward tools in Power Query. Once you understand how to use them, integrating financial and operational data becomes much easier.
If you are building your first Xero or HubSpot reports in Power BI and need guidance, our onboarding sessions walk through real data models step by step, so you can build reports confidently and correctly.
