Forum Discussion
Xero reports
Hi
I'm very new to power BI and I'm struggling with my xero reports and getting them into a flat file in power query that I can just update monthly. I have attached the print out on a demo company which will hopefully explain my problem.
As you can see untder the date column there is the cost centres ie accounts payable, advertising and marketing etc. and then under the headings is dates.
I would need to put a custom column to make the descriptions run down along the dates and then change with the new cost centres as they change.
If there anyone who can help me. I'm trying to make and P&L dashboard that I can drill down into and update monthly.
Thank you in advance
Loretto ☘
| Date | Source | Description | Reference | Debit | Credit | Running Balance | Gross | VAT |
| Accounts Payable | ||||||||
| Opening Balance | 0.00 | 18.90 | 18.90 | 0.00 | 0.00 | |||
| 07 Apr 2023 | Payable Invoice | PowerDirect | RPT445-1 | 0.00 | 96.25 | 115.15 | 96.25 | 0.00 |
| 16 Nov 2023 | Payable Payment | Payment: Gateway Motors | 449 | 411.35 | 0.00 | (296.20) | (411.35) | 0.00 |
| Total Accounts Payable | 411.35 | 96.25 | (296.20) | (315.10) | 0.00 | |||
| Closing Balance | 0.00 | 8,687.14 | (296.20) | 0.00 | 0.00 | |||
| Accounts Receivable | ||||||||
| Opening Balance | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |||
| 05 Apr 2023 | Receivable Invoice | Ridgeway University | RPT200-1 | 500.00 | 0.00 | 500.00 | 500.00 | 0.00 |
| 13 Nov 2023 | Receivable Invoice | Boom FM | INV-0045 | 1,623.75 | 0.00 | 2,123.75 | 1,623.75 | 0.00 |
| Total Accounts Receivable | 2,123.75 | 0.00 | 2,123.75 | 2,123.75 | 0.00 | |||
| Closing Balance | 20,441.38 | 0.00 | 2,123.75 | 0.00 | 0.00 | |||
| Advertising & Marketing | ||||||||
| 26 Sep 2023 | Payable Invoice | SMART Agency - Design concepts for Oaktown Business Leader ad series | SM0195 | 1,666.67 | 0.00 | 1,666.67 | 2,000.00 | 333.33 |
| 27 Oct 2023 | Payable Invoice | SMART Agency - Prototype media banner & print mockups for Oaktown Business Leader ad series | SM0210 | 2,083.33 | 0.00 | 2,083.33 | 2,500.00 | 416.67 |
| Total Advertising & Marketing | 3,750.00 | 0.00 | 2,083.33 | 4,500.00 | 750.00 | |||
| Audit & Accountancy fees | ||||||||
| 19 Aug 2023 | Payable Invoice | Xero - Monthly subscription | RPT402-1 | 46.96 | 0.00 | 46.96 | 56.35 | 9.39 |
| 17 Oct 2023 | Payable Invoice | Xero - Monthly subscription | RPT402-1 | 46.96 | 0.00 | 46.96 | 56.35 | 9.39 |
| Total Audit & Accountancy fees | 93.92 | 0.00 | 46.96 | 112.70 | 18.78 |
1 Reply
- EugeneLebedev
Helper V
I have created P&L Dashboards with Xero data before. Here is the approach I use for extracting data form Xero automatically: https://vidi-corp.com/connect-xero-to-power-bi-tableau-excel-looker-studio/
Happy to chat if you are interested
