SAGE 50 ACCOUNTS UK TO POWER BI: SYNTHETIC P&L EXAMPLE Turing BI | prepared 4 October 2026 PURPOSE Follow 11 September activity lines from nominal mapping to seven P&L figures. The sample includes a sales credit note and two expense credits, plus boundary rows outside the reporting period. Use it to inspect signs, scope and totals. This is synthetic NORMALISED nominal activity for an educational reporting example. It is not a raw Sage export, a Sage database schema, a connector, a balanced journal, or a client result. Account codes and labels are illustrative. All amounts are GBP, with VAT and control-account movements excluded. FILES nominal-activity.csv 15 synthetic P&L lines, including four boundary rows. nominal-mapping.csv Seven illustrative nominal codes and their P&L groups. SagePL.pq Power Query M for one imported table named SagePL. measures.dax Eight separate DAX measures, including the row count. expected-results.csv Expected totals for the synthetic September fixture. README.txt Setup, reconciliation and limitations. BUILD THE LOCAL EXAMPLE 1. Use Power BI Desktop on Windows. Extract the ZIP into a local folder such as C:\Sage50PowerBIExample. Keep both CSVs together. These files use UTF-8, comma delimiters, ISO dates (YYYY-MM-DD), and a decimal point. 2. In Desktop, create a Blank query from Get data. In Power Query's Advanced Editor, replace the starting code with the full contents of SagePL.pq. Edit FolderPath to your extracted folder, without a trailing backslash. The query reads the two CSVs from your local computer. 3. Name the query exactly SagePL. Confirm the preview contains 11 rows, from SYN-003 to SYN-013. The period is TransactionDate >= 2026-09-01 and TransactionDate < 2026-10-01. August 31 and October 1 rows are excluded. Keep the query enabled for load, then choose Close & Apply. This query is fixed to September 2026. To report another month, provide the matching activity extract and change both PeriodStart and PeriodEndExclusive in the M query. 4. On the loaded SagePL table, use New measure to add each definition from measures.dax separately. Start at 'Revenue GBP =', through its closing parenthesis; then add the next definition in a new measure. Keep the names exactly as supplied, because the profit and margin measures refer to them. 5. Format the five GBP measures as currency, GBP, with two decimal places. Format Gross margin % and Operating margin % as percentages with one decimal place. Format Included rows as a whole number. 6. Add a card for each of the seven financial measures, plus Included rows. With all page, report and visual filters cleared, compare the cards with expected-results.csv. A ratio of 0.625 should display as 62.5%, not 0.6%. 7. Add a detail table with ActivityRowID, TransactionDate (the date field, not its automatic date hierarchy), NominalCode, NominalName, PLGroup, DebitGBP, CreditGBP and SignedAmountGBP. Use 'Don't summarise' for the amount fields and turn off totals on this detail table: adding revenue and positive expense display values together is not a profit calculation. EXPECTED SEPTEMBER RESULTS Revenue GBP 48,000.00 Cost of sales GBP 18,000.00 Gross profit GBP 30,000.00 Gross margin % 62.5% Overheads GBP 12,000.00 Operating profit GBP 18,000.00 Operating margin % 37.5% Included rows 11 TRACE THE CREDITS Revenue is credit minus debit: 32,000 + 18,000 - 2,000 = 48,000. SYN-012 is the sales credit note, represented by a debit to the revenue code. Cost of sales is debit minus credit: 12,000 + 6,500 - 500 = 18,000. SYN-010 is a supplier credit against the illustrative materials account. Overheads are debit minus credit: 2,000 + 800 + 900 + 8,500 - 200 = 12,000. SYN-011 is a credit against the illustrative software account. Gross profit = revenue - cost of sales = 30,000. Operating profit = gross profit - overheads = 18,000. Each margin divides its profit by revenue. DIVIDE returns blank for a zero denominator. These are the definitions for this small example; operating profit here is before finance costs and tax, which are outside its scope. QUERY CHECKS AND MODEL SHAPE ActivityRowID identifies a normalised nominal LINE, not necessarily a whole source transaction. TransactionDate is a date. NominalCode remains text. DebitGBP, CreditGBP and SignedAmountGBP use fixed-decimal currency types. Each activity row has one positive debit or credit; the other amount is zero. The M query checks the supplied column names, required values, duplicate row IDs, duplicate mapping codes, supported P&L groups and missing nominal mappings. It validates the full input before selecting the reporting period. Conversion errors in malformed dates or amounts also stop the query. The two CSVs join on NominalCode before loading. Only SagePL is loaded, with: ActivityRowID, TransactionDate, NominalCode, DebitGBP, CreditGBP, NominalName, PLGroup, SignedAmountGBP. There is no relationship to configure in this single-table learning model. Revenue's SignedAmountGBP is credit minus debit; expense SignedAmountGBP is debit minus credit. The measures filter by PLGroup before summing this field. KEEPFILTERS respects existing filters. Use an unfiltered report to reproduce the stated totals. Category selections can change the displayed margins. BEFORE ADAPTING THIS TO A SAGE FILE Sage 50 Accounts UK offers a Transaction CSV (Extended) report through Transactions > Reports > Transactions > Transaction CSV (Extended), then Export, choose CSV, and confirm the report criteria. See Sage's source below. That export does not automatically match these sample columns. Check the fields, transaction types, date basis and nominal detail available in your version. A normalisation step must handle nominal splits, credits, reversals and posting corrections, and create a stable unique ID for each nominal line. Do not rename a transaction total as a nominal-line amount without checking how it was split and posted. Agree the nominal-code mapping with the person responsible for the accounts. Exclude balance-sheet/control accounts and VAT from this P&L subset. Include the relevant P&L adjustments and use the same entity, period, date basis, posting status and account coverage when reconciling to Sage's reports. This example filters by transaction date in a calendar month. An accounting period, posting date, adjustment period or financial year can require a different rule. Changing the sample dates alone does not resolve that choice. KNOWN LIMITS AND VALIDATION The model is a single-currency, one-month teaching example with synthetic activity. It omits balance sheets, opening balances, cash flow, VAT, stock valuation rules, accrual policies, finance costs, tax and consolidation. It is not a complete set of financial statements or accounting advice. The downloadable CSV arithmetic, mapping coverage, boundary rows and ZIP contents were checked independently using a dependency-free Python validator. The M and DAX were reviewed against Microsoft documentation. They have not been executed in Power BI Desktop or the Power BI Service in this environment. Validate the query and measures in your own Desktop installation before use. There is no .pbix report in this bundle. It does not connect to Sage, publish a report, configure sharing, or set up scheduled refresh. A service refresh of local files needs its own supported access and refresh configuration. SOURCES Sage: exporting transactions (Sage 50 Accounts UK) https://gb-kb.sage.com/portal/app/portlets/results/viewsolution.jsp?solutionid=200427112357443 Microsoft: Text/CSV connector https://learn.microsoft.com/en-us/power-query/connectors/text-csv Microsoft: Power Query data types https://learn.microsoft.com/en-us/power-query/data-types Microsoft: Csv.Document and Table.NestedJoin https://learn.microsoft.com/en-us/powerquery-m/csv-document https://learn.microsoft.com/en-us/powerquery-m/table-nestedjoin Microsoft: CALCULATE, KEEPFILTERS and DIVIDE https://learn.microsoft.com/en-us/dax/calculate-function-dax https://learn.microsoft.com/en-us/dax/keepfilters-function-dax https://learn.microsoft.com/en-us/dax/divide-function-dax Guide: https://turingbi.com/insights/connect-sage-to-power-bi