Purchase Order, Receipt and Invoice Reconciliation
A three-way reconciliation checks whether the purchase order, warehouse receipt and supplier invoice agree before an invoice is cleared for payment.
Capabilities
The tools we use and the job each one does.
SQL, Power BI, Python, Excel and Power Query solve different parts of a reporting problem. The important question is not which logo is on the page, but whether the chosen tool makes the report clearer, easier to update and easier to check.
Every example links to the actual report, workbook, code or check results available for inspection.- 01File or recordInvoiceLineID 1
- 02QueryTyped invoice and purchase rows
- 03Reporting modelDeclared fact grain
- 04Check18 of 18 checks passed
- 05Report or decisionMay 2016 report ready
- 01 · SQLInvoice, order and receipt rowsDeclare each fact grain, apply shared joins and reconcile the totals.Example: Wholesale Sales, Margin and Stock Reporting Mart
- 02 · Power BIA review question and an agreed measureConnect the measure to a model and lead from the headline to the affected record.Example: Purchase-to-Pay Flow and Bottleneck Review
- 03 · PythonFailed records and time-ordered forecastsExecute named rules or compare forecasts with a baseline, then retain the result.Example: Order and Delivery Data Quality Checks
- 04 · Excel and Power QueryRegional files, adjustments and a monthly workbookCombine expected files, separate approved changes and record the issue decision.Example: Monthly Sales and Margin Workbook Rebuild
01 / 04
SQL
SQL gives invoice lines, orders, receipts and reference data a declared grain before they become shared reporting totals.
Review the SQL method- Report, file or record
- Retain source rowsInvoice, order, receipt and reference records keep their original keys and load details.
- Transformation or check
- Type and standardiseDates, identifiers and values are prepared once in named staging models.
- Final check
- Reconcile reported totals to retained source records and preserve failed rows.
See it in practice
Wholesale Sales, Margin and Stock Reporting Mart
A reporting analyst prepares the wholesaler’s May sales, margin and stock report from invoice lines, supplier orders and warehouse movements, with each figure checked before finance, sales and warehouse managers use it.
View Wholesale Sales, Margin and Stock Reporting MartScope and limitationSQL can make a rule repeatable and a total traceable. It cannot decide the business meaning of an order, delivery or financial measure without the people responsible for that process. The cited build executes in DuckDB; the separate T-SQL deployment package is retained and linted, but remote SQL Server execution is not claimed.
02 / 04
Power BI
Power BI connects an agreed management question to a dimensional model, explicit measures and the records needed to explain the result.
Review the Power BI method- Report, file or record
- Name the decisionDefine the meeting question and the people who must answer it.
- Transformation or check
- Set the fact grainKeep assessments, events, ledger entries or workforce snapshots at a consistent level.
- Final check
- Validate totals, interactions, accessibility, performance and refresh ownership before release.
See it in practice
Purchase-to-Pay Flow and Bottleneck Review
A six-page Power BI report and verified event-log build using the complete BPI Challenge 2019 source to show purchase-order routes, waiting time, repeated work, payment risk and individual item history.
View Purchase-to-Pay Flow and Bottleneck ReviewScope and limitationPower BI cannot repair missing records or settle disputed definitions by presentation alone. Source preparation, measure ownership, refresh controls and the choice to publish or hold a report remain part of the reporting system.
03 / 04
Python
Python turns reporting rules and analytical methods into repeatable runs with declared inputs, retained results and tests another analyst can inspect.
Review the Python method- Report, file or record
- Load the declared inputsThe run records files, row counts, period, checksum where practical and run identifier.
- Transformation or check
- Execute named rulesCompleteness, validity, consistency and timeliness checks use report-specific thresholds.
- Final check
- Evaluate forecasts in time order against a simple baseline and retain uncertainty as well as point estimates.
See it in practice
Order and Delivery Data Quality Checks
A repeatable set of checks that stops incomplete or contradictory order records from entering the monthly service report and gives each failed row a specific correction reason.
View Order and Delivery Data Quality ChecksScope and limitationA passing Python check proves only that the stated rule or evaluation passed for the stated records. Historical forecast accuracy does not prove deployed performance, causation or that an automated operating decision is safe.
04 / 04
Excel and Power Query
Excel and Power Query are used when people need a familiar workbook without hiding file intake, transformation logic, adjustments or checks.
Review the Excel and Power Query method- Report, file or record
- Check the expected filesThe refresh confirms the period, sender, filename and required columns before combining data.
- Transformation or check
- Run staged queriesReusable functions, filters, types and reference joins remain visible in Power Query.
- Final check
- Stop or limit the refresh when an expected file is missing or its structure has changed.
See it in practice
Monthly Sales and Margin Workbook Rebuild
An Excel and Power Query rebuild that replaces the monthly copying of regional sales files with a repeatable refresh, visible adjustments and a checked sales and margin pack.
View Monthly Sales and Margin Workbook RebuildScope and limitationA workbook is appropriate when its scale, users and controls fit the routine. It should not conceal unstable source structures, uncontrolled access or transformations that need a managed data platform.
Two capabilities in one example
Prepare the delivery calculation once, then use the same rule in the report.
Five reports calculate on-time delivery differently. The example recalculates each result from the order records, agrees one definition and carries that same population and completion date into the report measure.
- 01Five delivery results disagreePopulation, date and grain differ between reports.
- 02Order records are recalculated at the agreed grainSQL
- 03Approved result: 38.81%Zero unexplained residual remains in the numerical bridge.
- 04The report measure adopts the same rulePower BI
- 05Why five reports show different on-time delivery ratesOpen the five-report reconciliation
Capabilities used together
A connection appears only when one Project uses both.
These are not suggested technology bundles. Each route below is supported by a project example that uses more than one discipline.
Supplier Invoice Matching and Review
The invoice asks for payment, but the purchase order and warehouse receipt must agree before the reviewer can release it.
Order-to-Delivery Metric Dictionary
Thirty versioned order-to-delivery metrics connect business definitions to named order and delivery fields, executed SQL, retained DAX measures and the reports that use them.
Why five reports show different on-time delivery rates
A row-level reconciliation explaining why the SQL reporting mart, Power BI model, Excel management pack, operational extract and manually maintained report show different on-time delivery percentages.
Reporting Data Issue and Retest Register
A reporting data incident register that holds an affected report section, separates the product-record correction from independent retest and records release, limitation or reopening without erasing history.
Cycle Hire Demand and Service Planning
A cycle-hire service planner reviews expected departures before the next operating shift. The study compares one, three and six-hour forecasts, shows where errors grow and keeps uncertain stations for human review.