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