1 / 19

How to Create a P&L from Xero in Power BI

Learn how to transform your Xero data into a dynamic, branded Profit & Loss statement in Power BI.<br>This presentation walks through each step, from exporting and prepping your Excel file to designing a client-friendly dashboard with visuals, slicers, and automated refreshes.<br><br>Perfect for small business owners, accountants, and consultants who want financial clarity, automation, and visual impact.<br><br>Full blog guide:<br>https://www.selectdistinct.co.uk/2025/09/29/how-to-create-a-pl-from-xero-in-power-bi/<br><br>#PowerBI #Xero #ProfitAndLoss #FinanceDashboard #SmallBusinessTools #SelectDistinct #Accounting<br>

Simon52
Download Presentation

How to Create a P&L from Xero in Power BI

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. How to: Create a P&L from Xero in Power BI Power BI Tips and Timesavers https://www.bensound.com/ (energy) https://www.bensound.com/

  2. Whether you're a small business owner or a finance consultant, having a clear, visual Profit & Loss (P&L) dashboard can transform how you understand and communicate financial performance. In this guide, we will walk you through how to extract data from Xero and Excel to build a P&L statement in Power BI, with no coding skills needed.

  3. Why Use Power BI for P&L Reporting? • Visual clarity: Replace static spreadsheets with interactive charts and slicers. • Real-time insights: Refresh data from Xero and Excel with a click. • Custom branding: Tailor dashboards to your business identity or client needs.

  4. Xero Reports you need: • Account Transactions • Chart of Accounts

  5. Account Transactions Report • Step 1: Extracting the Account Transactions data - Xero to Excel: • Manual Export: • 1. Log into Xero and select the company • For this guide, we will be referring to the Demo Company (UK) • 2. Locate to the Reporting tab and select Account Transactions

  6. Account Transactions Report • Step 1: Extracting the Account Transactions data - Xero to Excel: • 3. The below should be displayed as: • Accounts: Select all accounts • Date Range: This Financial Year (adjust accordingly) • Columns: 12 Columns Selected • Grouping/Summarising: Group By: Account Code & Name • 4. Re-order columns to your preference • 5. Export the report and save the file as an Excel Workbook

  7. Chart of Accounts Report • Step 2: Extracting the Chart of Accounts data - Xero to Excel: • Navigate to the Accounting tab • Underneath Accounting Tools select Chart of Accounts • Export the report and save the file as an Excel Workbook

  8. Step 3: Importing data from Excel to Power BI: • Open Power BI and navigate to Get Data • Select the dropdown bar and select Excel Workbook for Account Transactions • Select Load Data • The data table should be visible in Power BI • Repeat these steps for importing the Chart of Accounts file

  9. Step 4: Data Cleansing in Power Query • When you first import the file, it imports with all the header rows and sub totalsWe will remove these to make it into a clean reporting table

  10. Step 4: Data Cleansing in Power Query • Step 1 – Remove top rowsremove the top 4 rowsStep 2 – Use first row as headersStep 3 – Remove empty (spacer) rows

  11. Step 5: Creating the relationship of data in Power BI: • Now all the data is accessible in Power BI, we need to connect this together. • Navigate to Model View on the left-hand side of the Power BI screen • Select Manage Relationships and New Relationship • From table: your_account_transactions_table • To table: your_chart_of_accounts_table • Cardinality: Many to one (*.1) • Cross-filter direction: Single Ensure that the Account Code Column and Code Column are selected so the relationship can be created.

  12. Converting Debits and Credits to a clearer value • Us accountants can be a strange bunch!By default, Sales show as credits (Negative), and expenses as Debits (Positive)We want to change this to make the reporting much more intuitive, so we will create a calculation which checks the type of account and presents in an understandable way Sales + Expenses -

  13. Step 6: Creating a column to calculate the value: • Open the Table View on the left-hand side of the Power BI screen • Locate to Table Tools and select New Column for the Account Transactions dataset • New Column Name = Value • Now look at your chart of accounts to see how the coding works, in our example all of the P&L codes are in the range below 600So we will only change those • Custom Column Formula = if [Account Code] < 600 • then ([Debit] – [Credit]) * -1 • else (Debit] – [Credit])

  14. Step 7: Organising account type sort order: • Create a new table • 1. Name of table: Account Type Sort • Enter Data • 2. Two columns: Type & Sort • 3. Rows underneath Type: Asset, Expense, Liability, Revenue • 4. Rows underneath Sort: Apply a numbering system for the layout of a P&L & Balance Sheet • 5. Load the data

  15. Step 8: Creating the relationship of Account Type Sort data • We need to be able to connect the new data table to the account transactions. • Navigate to Model View on the left-hand side of the Power BI screen • Select Manage Relationships and New Relationship • From table: your_account_transactions_table • To table: your_account_type_sort_table • Cardinality: Many to one (*.1) • Cross-filter direction: Single Ensure that the Account Type Column and Type Column are selected to create the relationship

  16. Step 9: Building the P&L dashboard in Power BI : • In Report View • 1. Select the Matrix Table from Visualizations • 2. Rows: • Type (From ‘Account Type Sort’ table) • Name (From ‘Chart of Accounts’ table) • 3. Columns: • Date (From ‘Account Transactions’ table) • Month • 4. Values: • Sum of Value • 5. Apply basic filtering: Select Revenue & Expense • 6. Sort this visual by: Type

  17. Power BI Dashboard Example: To check the P&L balances in Power BI works, check this against the Profit & Loss Report in Xero

  18. Final Thoughts: • Creating a P&L dashboard in Power BI isn’t just about numbers, it’s about clarity, trust, and empowering better decisions. • Having the ability to create the reporting the way you want to see it can be really powerful for you.

  19. For more Tips, Tricks and TimesaversVisit our blogBusiness Analytics Blog – Select DistinctCredit: elle.harrison@selectdistinct.co.uk

More Related