QUICKBOOKS TO POWER BI: NORMALISED P&L EXPORT TEACHING PACK
Turing BI | prepared 21 October 2026

PURPOSE AND SCOPE

Trace seven fictional account summaries into a monthly P&L subset, including
signed sales returns and supplier credits. Compare the expected figures before
adapting the approach to your own reporting requirements.

The data is NORMALISED FICTIONAL data. It is not a raw QuickBooks export, a
transaction journal, a QuickBooks schema, a connector, or a client result.
The account keys, account names, company and amounts are illustrative.

Each row is one LEAF account's summary amount for the same report snapshot:
company DEMO-CO-01, 1 to 30 September 2026 inclusive, accrual basis, USD.
AmountUSD uses positive revenue and positive expense display amounts.
The two negative amounts reduce their respective groups.

FILES

normalised-pnl.csv  Seven fictional leaf-account summaries and report context.
report-controls.csv  Eight expected measures with units.
measures.dax  Eight separate illustrative DAX measure definitions.
README.txt  Import steps, reconciliation and limits.

BUILD THE LOCAL EXAMPLE

1. Extract the ZIP to a local folder. In Power BI Desktop on Windows, choose
   Get data > Text/CSV and select normalised-pnl.csv. Select UTF-8, a comma
   delimiter and Transform Data. The file has one header row and nine columns.

2. Confirm the headers match the CSV. Set CompanyKey, Basis, Currency,
   AccountKey, AccountName and PLGroup to Text. Set PeriodStart and PeriodEnd
   to Date. Set AmountUSD to Fixed decimal number. Use Change type > Using
   locale with English (United States) where date or amount conversion needs
   a locale. The dates use YYYY-MM-DD and amounts use a decimal point.
   Inspect any automatically added type step before applying your types.

3. Name the query exactly QBOExport. Confirm seven rows, one company, one
   period, one basis and one currency. Choose Close & Apply. This is a single
   imported table; there is no relationship to configure in this example.

4. On QBOExport, choose New measure and create each definition in measures.dax
   separately. Keep the measure names as supplied: the profit and margin
   definitions refer to those names. Loading the DAX text as data does not
   create the measures.

5. Format the five USD measures as US dollar currency with two decimal places,
   the two margin measures as percentages with one decimal place, and Imported
   rows as a whole number. A raw ratio of 0.60 should display as 60.0%.

6. Add one card per measure. Clear page, report and visual filters. Compare
   all eight cards to report-controls.csv. Keep controls outside QBOExport;
   they are comparison values, not additional account-summary rows.

7. Add a detail table with all nine source columns. Select the date fields
   rather than their automatic date hierarchies. Set AmountUSD to Don't
   summarise and turn off the detail table's totals. Summing revenue and
   positive expense display amounts together does not calculate profit.

EXPECTED UNFILTERED RESULTS

Revenue USD                 50,000.00
Cost of sales USD           20,000.00
Gross profit USD            30,000.00
Gross margin %                  60.0%
Operating expenses USD      12,000.00
Operating profit USD        18,000.00
Operating margin %              36.0%
Imported rows                      7

Revenue = 52,000 - 2,000 = 50,000.
Cost of sales = 20,500 - 500 = 20,000.
Operating expenses = 2,000 + 9,500 + 500 = 12,000.
Gross profit = revenue - cost of sales.
Operating profit = gross profit - operating expenses.
Each margin divides its profit by revenue. DIVIDE returns blank when its
denominator is zero. KEEPFILTERS respects existing group filters; selections
can change the figures and margins. Use an unfiltered view for these controls.

ADAPTING A QUICKBOOKS REPORT

Start with the P&L report for the agreed company, dates, accounting basis,
currency and account coverage. Preserve those settings with the exported file.
Map the report's LEAF account summaries to a documented normalised schema and
P&L groups. Check each mapped amount against the source report, including the
signs of returns and credits. The sample's EX- account keys are fictional.

Keep headings, blank lines, subtotal rows, group totals and report totals out
of the leaf-account table. Including both leaf amounts and their parent totals
double-counts the same figures. Reconcile group totals and the defined profit
figures to the corresponding source report before using the model.

A report export is a snapshot. Replace the same snapshot rather than appending
overlapping exports. For multiple snapshots, define the unique company, period,
basis, currency and account key, plus a deliberate version/replacement rule.
Keep cash and accrual reports and different currencies distinct. These measures
alone do not select the latest version or prevent overlapping periods.

REFRESH AND LIMITS

A Power BI refresh reads the file available through its configured source.
Obtaining a newer QuickBooks export is a separate upstream step. Service
refresh also requires a supported file location, credentials and any required
gateway configuration. This pack supplies neither that configuration nor an
automatic QuickBooks export process.

This one-company, one-month USD subset omits tax, finance costs, other income,
balance-sheet/control accounts, cash flow and consolidation. It is not a
complete set of financial statements or accounting advice. Review coverage,
classification and profit definitions with the person responsible for accounts.

The supplied CSV arithmetic, context, account-key uniqueness and ZIP contents
were checked independently with a dependency-free Python validator. The DAX
has not been executed in Power BI Desktop or Service. This pack contains no
Power Query M, PBIX report, native screenshots or tested QuickBooks connection.
No QuickBooks account, account export or refresh was examined for this example.
Validate the import and measures in your own Desktop installation before use.

SOURCES

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: KEEPFILTERS and DIVIDE
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-quickbooks-to-power-bi
