TLDR: a client needed QuickBooks Online and Salesforce data in Power BI. That turned into an Azure Data Factory pipeline pulling thirty eight QBO tables into Azure SQL, a set of reconciled P&L views that match the source system to within a rounding error, and a second workstream on VAT reporting that's deliberately on hold until the underlying data is clean enough to build on.
A client, came to me needing two things that sat side by side but weren’t quite the same project: a proper Power BI reporting layer pulling together QuickBooks Online and Salesforce, driven by their technical lead, and a VAT reporting requirement, driven by the business owner and her VAT consultant. Same underlying data, two different problems.
The plumbing
Salesforce to Power BI was the easy half. Their technical lead already had that covered using the native Power BI Salesforce connector, so that side of the pipeline was never mine to build.
QuickBooks Online was the real work. I set up an Azure Data Factory pipeline with a ForEach loop pulling thirty eight QBO tables into an Azure SQL database. Each table gets truncated by a pre-copy script before every load, which sounds like a small detail but matters a lot: without it, reruns duplicate everything, and a reporting pipeline that silently doubles your invoices on a retry is a bad thing!
The pipeline currently runs on a manual trigger rather than a nightly schedule, and that’s deliberate: I wanted the technical lead to look at real numbers in Power BI and confirm they matched what he expected before I set anything to run unattended. Power BI is connecting with admin level credentials for now too, an accepted compromise on a free tier Azure SQL instance where proper Entra, role-based authentication isn’t available. Neither of those is where I want to land long term, but they were the right tradeoffs to get something usable in front of the client quickly (and cost effectively).
Getting the numbers to actually match
The two views that matter most to the technical lead are sales invoiced and cost of goods sold, both built to be date aware rather than hardcoded to a range, so the same view keeps working as new data lands.
Reconciling those against QuickBooks’ own P&L was where the real work was. Over an eight month stretch, sales invoiced came out within about a thousand pounds of QuickBooks’ own figure, and cost of goods sold within a couple of thousand, on totals in the low millions. Both variances traced back to exchange rate rounding on foreign currency transactions, and once I understood why, I was comfortable calling that acceptable rather than chasing it further.
Getting there meant digging into some genuinely unintuitive corners of the QBO data model. The cost of goods sold account type is stored as a single word with a lowercase letter in the middle, not the two capitalised words you’d guess at. Foreign VAT liability accounts sit under a parent called Overseas VAT and are typed as other current liability, not income, which matters enormously if you’re building a revenue view and don’t want foreign VAT collected on an invoice mistaken for foreign revenue earned. And foreign currency logic turned out to differ by transaction type entirely: invoices need line level apportionment because a single invoice can carry lines across several account types including that VAT liability line, while bills can use a flat exchange rate multiplication because the home currency cost field is simply zero on every bill record, for reasons QuickBooks doesn’t explain.
The VAT workstream, and why it’s paused on purpose
The VAT reporting side is a different kind of problem, and understanding it properly meant understanding how the client actually uses QuickBooks first. Every sub customer in their system is really a project, raised for a single Salesforce opportunity, invoiced exactly once, and never touched again. QuickBooks doesn’t have a native concept of project costing, so the client has repurposed the sub customer structure to get one, which is a sensible workaround but an easy thing to misread from the outside as hundreds of real, ongoing customer relationships with patchy address data.
Once that clicked, the right response became obvious. There’s no value in working backwards through hundreds of historical projects to fill in a country field on records that will never generate another invoice or another VAT calculation. That data simply won’t be reported on again, so cleaning it up would be effort spent for its own sake.
What matters instead is every project created from here on. The client’s project creation form now has a proper country dropdown and an EC VAT number field, and the business owner’s team knows to populate both for every new project going forward. I’ve also built a reference table mapping the various ways country names have historically been entered into standardised names and UK, EU, or rest of world regions, ready for when it’s needed.
The plan now is simply to wait. Give the new process three to six months to accumulate a clean run of properly tagged projects, then build the VAT report and a pre flight check against that clean data, rather than layering reporting logic on top of a structural quirk I only fully understood after the fact.
What’s left
Once the technical lead confirms the Power BI figures look right, the pipeline moves to a nightly schedule. A new table capturing the EC VAT custom field needs adding to the pipeline. The VAT report’s transaction classification logic is agreed in principle, country determines local, EU, or export, presence of an EC VAT number on the parent customer determines B2B versus B2C, and goods versus services will come from a second custom field the client still needs to build. And the two data quality spreadsheets need to actually land in the business owner’s inbox, which is the kind of small, unglamorous, easily deferred task that this whole project is quietly a case study in not deferring.