Free tool

Xero Profit and Loss to Power BI table

A Profit and Loss exported from Xero is laid out for reading: section headings, subtotals and one column per month. Load it here and get the flat table Power BI wants instead, one row per account and period, as a CSV. It runs in your browser, so your files never leave your computer.

1. Export the report from Xero

Go to Reporting > All reports > Profit and Loss. For months side by side, pick Current financial year by month under Common formats (or set a date range and Compare with previous months), click Update, then Export and choose Excel.

2. Load it here

Several companies or years? Select all the files at once and they end up in one table. No file to hand? .

Or paste the rows from a spreadsheet

What the tool does

The tool does the clean-up you'd otherwise build in Power Query. First it finds the heading row with the periods, then it reads the report line by line:

  • Section headings such as Trading Income or Operating Expenses become a Section column, so you can still group accounts the way Xero does.
  • Totals and calculated lines (Total Operating Expenses, Gross Profit, Net Profit) are dropped. Power BI adds them up again from the accounts, so keeping them would count everything twice.
  • Period columns are unpivoted: instead of one column per month you get a Period column with the heading as Xero wrote it (Oct 2026), a Period start date (2026-10-01) that Power BI reads as a date, and one Amount column. Year to date, total and variance columns are left out, and a month that's already in an earlier file isn't added twice.
  • The organisation name comes from the top of each file, so files from several companies can share one table.

In Power BI

Choose Get data > Text/CSV, pick the file and click Load. Set Period start to the Date type if Power BI hasn't already. Amounts keep Xero's signs: income and expenses both show as positive numbers, so a profit measure subtracts the expense sections from the income ones. If you compared tracking categories in Xero rather than periods, the Period column holds the tracking option and Period start stays blank.

To build up history, save each month's CSV (one month per file) in a SharePoint or OneDrive folder and load it with Get data > SharePoint folder. Power BI combines the files when it refreshes. The other options, connector apps and the Xero API, are in how to connect Xero to Power BI.

In Google Sheets or Excel

The CSV works for pivot tables too. In Google Sheets, choose File > Import, upload the file, then Insert > Pivot table. In Excel, open the file and choose Insert > PivotTable. Ways to keep a sheet up to date without exporting by hand are in how to connect Xero to Google Sheets.

Which files the tool reads

  • Excel or CSV. The tool reads .xlsx and .csv files. Export old .xls files again as .xlsx.
  • Custom layouts work too, as long as each account is one row with the periods across the top. Accounts inside a group get the group's name as their Section.

Nothing is uploaded or stored. Your browser reads the files, and they're gone when you close the tab.