Connect Sage to Power BI: Sage 50 & 200 UK Guide

Your finance report should let you follow a total back to the entries behind it. Connecting Sage to Power BI starts with choosing a supported way to read your ledger, then agreeing how dates, nominal codes and credits become report figures.

Choose your edition first. The Sage 200 API-backed report route and the Sage 50 Accounts UK CSV example below cover different workflows. Microsoft's Power Query catalogue and Sage's own report templates are separate resources.

For a commissioned finance report, our UK Power BI consultancy covers source connections, the reporting model and checks against your Sage figures. Use the example to identify the report and totals you want your team to work from.

Which Sage product do you run?

Product names and editions matter. This walkthrough covers the UK Accounts product; other regional products called Sage 50 have different interfaces and data structures.

Sage 200 Professional can run on premises or in a hosted environment. Its setup differs from Standard, so check Sage's edition-specific API requirements. For new Intacct integrations, Sage recommends its REST API; its XML API remains supported.

Sage 200 to Power BI

Sage provides a Customers report template with an embedded API connection. It reads Sage 200 through its API, rather than connecting directly to SQL Server. Follow Sage's Power BI report instructions for the supplied template and supported setup. That is a different workflow from this guide's Sage 50 CSV exercise.

1. Check the edition and API access. Standard requires users enabled for API access. Professional requires configuration for the installed version. Sage's current API FAQ describes the recommended Native API route for supported Professional releases and the API Developer Programme requirements for development.
2. Start from Sage's template. Confirm the connected company and inspect the customer data before extending its queries. Sage's walkthrough shows how to add another entity using the template's existing API query function.
3. Check coverage and refresh. List the entities, history and reporting periods your model needs. Test a published refresh with the authorised setup and compare results with the corresponding Sage reports at the same cutoff.

For a management P&L, document nominal mappings, periods and signs separately from the customer-report connection. A successful connection does not establish that every required finance entity is available or that the P&L reconciles. The sample CSV download below has been checked as a teaching fixture; it is not a Sage 200 template or a tested live API integration.

Worked example: Sage 50 Accounts UK to a monthly finance report

Illustrative example using fictional data. The files are a small, normalised teaching dataset created by Turing. They represent selected profit-and-loss nominal activity after export mapping. They are not a native Sage export, a customer case study or a complete double-entry ledger.

The scope is one fictional company, September 2026, GBP, excluding VAT. The sample uses transaction dates within the calendar month. It assumes the included rows contain all revenue, cost of sales and overheads for this exercise. Balance-sheet accounts, cash flow, tax, interest, consolidation and currency conversion sit outside the example.

Download the worked-example ZIP. It contains two CSV inputs, Power Query import code, DAX measures, expected results and instructions. You can also inspect the activity CSV, nominal mapping and setup instructions individually. The pack provides code and data to build a report in Power BI Desktop; it does not contain a PBIX report.

1. Choose the export and preserve the source

For your own Sage 50 Accounts UK data, Sage documents this export route: Transactions → Reports → Transactions → Transaction CSV (Extended). Choose Export, select CSV and set the report criteria. Follow Sage's export instructions for your installed version.

Keep the original export and record the company, criteria and extraction time. Export a matching Sage profit-and-loss or nominal activity report for your control totals. Sage's Transactional Profit and Loss report supports transaction criteria and requires Standard or above. Agree the date basis and chart-of-accounts layout with the person who owns the accounts.

For this exercise, use the supplied CSVs. The supplied import expects the teaching schema below. Applying it to your own Sage export requires a separate mapping step: identify each nominal posting, its transaction type and debit or credit effect. Preserve split lines and reversals, and separate P&L entries from VAT and balance-sheet control accounts. Changing column names alone is insufficient.

2. Map the nominal activity

The activity file keeps debits and credits in separate GBP columns. The mapping file assigns each included nominal code to Revenue, Cost of sales or Overheads. Those categories belong to this example; use your own chart of accounts for delivery.

A sales credit note creates a debit against revenue and reduces sales. An expense credit creates a credit against the expense account and reduces costs. Sage documents the debit and credit effects of its transaction types. Keeping those signs visible makes the checks easier to follow.

3. Import the sample into Power BI Desktop

1. Extract the ZIP into a local folder on your Windows machine. Read README.txt first.
2. In Power BI Desktop, choose Get data → Blank query, open Power Query's Advanced Editor and paste the contents of SagePL.pq.
3. Change the folder setting at the top of the query to the folder containing the two CSVs. Name the query SagePL.
4. Inspect the output, then choose Close & Apply. The query applies types, checks identifiers and mappings, joins the account categories and keeps dates from 1 September up to, but excluding, 1 October 2026.
5. Add the measures from measures.dax one at a time with New measure, following the formats in the instructions.

For a manual import, Microsoft's Text/CSV connector instructions describe Get data → Text/CSV → Transform Data. That route still needs the transformations supplied in the query. Explicit types matter: account codes should stay as text, dates need the correct locale, and money can use Fixed decimal number.

The supplied fixture and expected arithmetic have been checked separately. The Power Query and DAX have not been executed in Power BI Desktop, and this example has not connected to a live Sage installation.

4. Reconcile the totals before building visuals

September revenue is £50,000 of sales postings on the credit side, less a £2,000 sales credit note, giving £48,000. Cost of sales is £18,000. Overheads total £12,000 after expense credits. Rows outside September are deliberately included in the input so you can check the period filter.

These are expected results for the fictional fixture, not independently exported Sage control totals. A production reconciliation compares the model with Sage's report at the same cutoff, first by nominal account and then by subtotal. A matching grand total can hide two offsetting errors.

Check that each input row appears once, each nominal code has one mapping, credit notes reduce the correct category and the August/October rows stay outside September. The sample query rejects duplicate identifiers and missing or duplicate mappings. Your production checks also need to cover extraction completeness and the treatment of deleted, reversed or later-adjusted entries.

5. Build a report that explains the result

Start with cards for Revenue, Gross profit, Gross margin and Operating profit. Add a table of nominal codes and amounts so a reader can trace each category back to its rows. Use the supplied measures and the exact table name from the instructions.

Open the report illustration at full size. This is a static illustration of the expected sample output, not a screenshot from Sage or Power BI.

The figures show how £48,000 revenue becomes £18,000 operating profit after £18,000 cost of sales and £12,000 overheads. A reader can inspect the costs and credits behind that result. This single-month example supplies neither a cash balance nor a trend; those need the appropriate accounts and reporting periods.

What changes when you need scheduled refresh?

A Desktop refresh reads the CSV currently on disk. Creating the next Sage export is a separate step. For another month, update both the source file and the period start/end settings in this teaching query. Its September filter is deliberately fixed. A recurring model needs a maintained date range or date table as well as fresh exports.

For a published model reading a local file or an on-premises ODBC source, plan a gateway, credentials and a machine that can reach the source. Microsoft's refresh guidance explains that connection. Moving a CSV to cloud storage changes the access route; the export still needs an owner or a supported automation. See our gateway and refresh guide for the operating checks.

Four ways to connect Sage to Power BI

A warehouse is the destination for extracted data, so it still needs a connection to Sage. A connector's suitability depends on its supported product and objects. For a wider model, our data integration service covers source data, reporting storage and checks.

Finance measures and checks to agree next

Once the P&L reconciles, expand according to the decisions you need to make: aged receivables for collections, cash movement for liquidity, or budget versus actual for spend control. Each needs its own data and definition. Use an agreed date model when moving beyond this fixed-month exercise.

• Accounting dates: agree calendar dates, financial periods and adjustment handling. Keep the same basis in Sage and Power BI.
• Nominal codes: map every relevant code once, and surface new codes for review.
• Transaction state: follow the product's posted-entry, deletion and reversal semantics when extracting activity.
• Multiple companies: retain the company key and agree inter-company eliminations before combining figures.
• Currencies: choose the reporting currency, rate basis and conversion date explicitly.

Discuss your Sage reporting setup

Discuss your Sage setup with your product and version, company count, required reports, country and timezone. Include the Sage report your figures must match and how often the data needs to refresh. The Trusted Numbers Review covers tracing figures and agreeing the reporting definitions; our pricing page describes the current delivery options.