Bank Reconciliation with Excel Power Query and Power Automate

The Business Situation

Harborstone Equipment Services is a fictional 58-person equipment maintenance and rental business. Its four-person finance department uses QuickBooks Online as its accounting system, online banking portals for three cash accounts, Excel for reconciliations, and Microsoft 365 for document storage and communication.

The monthly reconciliation process involves approximately 1,250 bank transactions and 1,180 accounting transactions across an operating account, payroll account, and merchant settlement account. The senior accountant prepares the reconciliations, the controller reviews them, and the finance director acts as the backup approver for high-risk exceptions or controller absences.

Each month, the senior accountant exports transaction data from the bank portals and QuickBooks Online. The files are copied into spreadsheets, reformatted, and compared using filters, formulas, sorting, and manual review. The method works at low volume, but it is difficult to reproduce consistently and depends heavily on the preparer remembering each step.

The business wanted to retain QuickBooks Online and Excel while introducing a controlled process for standardized imports, exact matching, reference-based matching, cautious fuzzy suggestions, exception review, sign-off, and evidence retention.

Note: This case study is provided as a representative example of the types of AI integration and digital transformation solutions Intelligex designs and delivers. Actual engagements are tailored to each client’s goals, constraints, existing systems, timeline, and available resources, so the approach, tools, and outcomes may vary.

The Existing Process

The original reconciliation followed the same general sequence each month, but many details were undocumented.

1. The senior accountant downloaded bank activity from three bank portals.
2. A transaction-detail report was exported from QuickBooks Online for each corresponding bank account.
3. The exports were copied into separate workbook tabs.
4. Dates, amounts, references, and descriptions were reformatted manually.
5. Transactions with identical amounts were filtered and compared visually.
6. Lookup formulas were added to find exact amounts or references.
7. Potential matches were highlighted using cell colors.
8. Unmatched transactions were copied to an exception tab.
9. Questions were sent through email to accounts payable, payroll, or operations.
10. The completed workbook was emailed to the controller for review.
11. Reviewer comments were returned by email, and the workbook was updated again.
12. A final copy was saved in a monthly finance folder.

The largest control issue was not that Excel was being used. The issue was that the workbook had no stable data model, deterministic matching order, controlled exception process, or reliable approval record.

What the New System Needed to Do

The finance team defined the requirements before selecting an automation method.

The design deliberately separated automated matching from accounting judgment. Exact and unique reference matches could be classified automatically. Fuzzy matches, timing classifications, bank errors, and approval remained human-controlled.

Implementation Approaches Considered

Expanded Excel formulas

Formulas such as XLOOKUP, COUNTIFS, and helper columns could improve the existing workbook. This option was inexpensive and familiar, but it did not adequately address transformation logic, repeated imports, matching order, approval routing, or evidence retention. Formula-based matches were also vulnerable to duplicate amounts unless substantial control logic was added.

Excel Power Query with Power Automate

Power Query could standardize both exports, calculate stable matching keys, enforce matching order, and produce repeatable output tables. Power Automate could manage run creation, approval routing, reminders, status updates, and evidence files. This approach retained the company’s existing tools and was appropriate for approximately 1,250 bank transactions per month.

Google Sheets and Apps Script

Apps Script could parse files, apply matching logic, and create approval notifications. It was not selected because the finance team already used Excel, SharePoint, and Microsoft identities. Introducing Google Drive would create another permission model and document repository without solving a specific requirement that Power Query and Power Automate could not meet.

Custom application

A custom application could support direct APIs, a database, granular permissions, and a purpose-built review interface. It was not justified for the current volume because the business would assume responsibility for application hosting, authentication, monitoring, backups, and ongoing development.

Dedicated reconciliation software

Dedicated software should be evaluated when transaction volume, entity count, regulatory requirements, or intercompany complexity increases. For Harborstone Equipment Services, the immediate requirement was a controlled monthly process rather than a broad financial-close platform.

The Selected Solution

The selected implementation used QuickBooks Online as the accounting source, Excel as the reconciliation interface, Power Query as the transformation and matching engine, SharePoint as the run register and evidence repository, and Power Automate as the workflow and approval layer.

QuickBooks Online remained the accounting authority. The implementation did not create journal entries, release payments, or alter accounting records. If a bank-only transaction required a QuickBooks entry, finance posted it in QuickBooks Online, exported refreshed ledger data, and reran the query.

The desktop version of Excel was retained because it provides the required Power Query authoring and refresh capabilities. Power Automate did not attempt to refresh the workbook’s Power Query connections. The preparer refreshed and validated the workbook before changing the run status to Ready for Approval.

This boundary avoided an unreliable assumption that an Excel Online action would refresh every desktop Power Query connection. Power Automate read the already-refreshed summary table and blocked approval if the workbook reported unresolved items or a nonzero control difference.

System Architecture and Data Flow

• Intake: Bank exports, QuickBooks Online exports, statement balances, and supporting documents uploaded to a SharePoint run folder.
• System of record: A SharePoint list stores one item per reconciliation run; the workbook stores transaction-level decisions and calculated results.
• Automation layer: Power Query performs data transformation and matching; Power Automate manages run workflow and approval.
• Document storage: SharePoint stores source files, the working workbook, approved copies, and approval evidence.
• Notifications: Power Automate sends approval, reminder, rejection, completion, and failure notifications through Outlook or Teams.
• Reporting: Excel output tables and optional Power BI reports summarize volume, match rates, exceptions, aging, and completion.
• AI layer: An optional approved language model proposes exception categories and summaries but does not approve matches or accounting treatment.

1. Create the run. The senior accountant creates a SharePoint list item with account, statement period, preparer, approver, and ending-balance information. Power Automate assigns the reconciliation ID, creates the folder structure, copies the workbook template, and returns the workbook link.
2. Collect source evidence. The preparer exports bank transactions and the corresponding QuickBooks Online account activity. Original files are uploaded without modification to the run’s Source folder.
3. Load canonical tables. Required columns are pasted or mapped into the workbook’s controlled bank and ledger import tables. Each row retains its source file and source row number.
4. Validate inputs. Power Query checks required fields, data types, reconciliation IDs, transaction identifiers, amount signs, and duplicate row IDs. Invalid input stops the refresh rather than producing partial matching results.
5. Apply exact matching. The query joins account, date, and amount. A match is accepted only when the combination occurs once on each side.
6. Apply reference matching. Remaining transactions are joined using account, signed amount, and normalized reference. Candidates must fall within the configured date tolerance and remain one-to-one.
7. Generate fuzzy suggestions. Remaining bank rows are compared with remaining ledger rows having the same account and exact amount within a narrow date window. Description similarity creates suggestions, not final matches.
8. Review exceptions. The preparer records decisions in a separate input table. The query validates that an accepted fuzzy pair is still eligible and that the ledger row is not used elsewhere.
9. Calculate controls. Power Query calculates matched counts, unresolved counts, timing differences, bank-error adjustments, adjusted bank balance, and the difference from the QuickBooks ending balance.
10. Request approval. The preparer refreshes the workbook, confirms the output, closes the file, and changes the SharePoint item to Ready for Approval.
11. Validate readiness. Power Automate reads the workbook summary table. If unresolved items remain or the difference is not zero cents, the run is changed to Blocked and returned to the preparer.
12. Route sign-off. If validation passes, Power Automate starts the controller approval. A secondary approval is added when policy-defined high-risk conditions apply.
13. Retain evidence. On approval, Power Automate updates the SharePoint run, copies the workbook to the approved folder, and writes a structured approval-evidence file.
14. Handle failure. A failed flow writes an error status and message to the run item, notifies the support owner, and leaves the source files and workbook available for controlled recovery.

Data Structure

The design uses one reconciliation run per bank account and statement period. Three accounts therefore produce three monthly run IDs, even when the finance team reviews them together.

Reconciliation run record

Transaction import and review records

The bank and ledger tables have a one-to-one relationship only after a match has been accepted. Before that point, they are independent transaction sets. The review table links to one bank row, one ledger row, or both. Power Query rejects duplicate active decisions for the same row.

Workflow Statuses and Ownership

A record moves backward when an approver rejects it, when a refreshed ledger creates new unmatched items, or when source evidence is replaced. A fuzzy suggestion never advances a record by itself. It becomes a match only when an authorized finance reviewer records an eligible decision.

Step-by-Step Implementation

Step 1: Prepare the Accounts and Permissions

1. Confirm that the finance users have authorized access to the required QuickBooks Online reports and bank exports.
2. Create a SharePoint site or restricted finance area with a document library named Finance Reconciliations.
3. Create security groups for reconciliation preparers, approvers, finance administrators, and read-only auditors.
4. Give preparers edit access to active run folders and the run list. Give approvers read access to source evidence and edit access only where approval requires it.
5. Restrict approved folders so ordinary preparers cannot silently replace approved evidence. Corrections should create a new version or formally reopened run.
6. Create a SharePoint list named Reconciliation Runs using the fields defined earlier.
7. Create a shared finance mailbox or monitored notification address for automation failures. Do not use an employee’s personal mailbox as the only support destination.
8. Create Power Automate connections using an authorized automation owner. Where organizational policy permits, use a controlled service identity rather than an employee likely to leave the business.
9. Create two test users, one preparer and one approver, with the same permission boundaries as production roles.
10. Create a test folder and test list view. Use masked sample transactions rather than live bank information during development.

The required Microsoft subscription must support SharePoint, desktop Excel Power Query, the necessary Power Automate connectors, and approval actions. Licensing differs by tenant and connector, so the implementation team should verify current entitlements rather than assume a named plan includes every feature.

No QuickBooks API credentials are required for the selected file-based design. If direct API extraction is added later, store credentials in an approved secret store and grant read-only accounting access wherever the API permits it.

Step 2: Build the Intake

The workbook template contains four controlled input tables:

• tblBankImport
• tblLedgerImport
• tblReviewDecisions
• tblRunControl

The ledger table uses the same fields, replacing BankTransactionID with QBOTransactionID and adding TransactionType.

QuickBooks report layouts can vary based on report choice, customization, locale, and product updates. During implementation, the team maps the actual exported columns to the canonical fields rather than relying on assumed headings. If the export provides separate debit and credit columns for the bank asset account, the canonical signed amount is generally calculated as debit minus credit. The team must verify this convention against known transactions before production use.

Original exports are retained in the SharePoint Source folder. The canonical tables are working data, not replacements for source evidence.

Incomplete submissions are blocked by Power Query validation. Duplicate IDs also stop the refresh. This is preferable to silently discarding malformed transactions.

The run naming standard is:

REC-YYYY-MM-ACCOUNTKEY

Examples:
REC-2026-05-OPERATING
REC-2026-05-PAYROLL
REC-2026-05-MERCHANT

Source file names begin with the same identifier:

REC-2026-05-OPERATING_BANK_ORIGINAL.csv
REC-2026-05-OPERATING_QBO_ORIGINAL.xlsx
REC-2026-05-OPERATING_BANK_STATEMENT.pdf

The bank portal and QuickBooks Online remain responsible for authentication and export access. The intake does not expose an anonymous form, so public spam prevention is unnecessary. SharePoint authentication, file permissions, and allowed file types provide the intake boundary.

Step 3: Create the System of Record

The SharePoint list is the run-level system of record. The Excel workbook is the controlled transaction-level reconciliation artifact.

Create the list with indexed columns for ReconID, Status, StatementEndDate, Preparer, and Approver. Enforce unique values on ReconID where supported by the selected SharePoint column configuration.

Create these filtered views:

• My Open Reconciliations: Preparer equals current user and Status is not Approved.
• Awaiting Approval: Status equals Approval In Progress.
• Blocked Runs: Status equals Blocked.
• Automation Failures: AutomationStatus equals Failed.
• Recently Approved: Status equals Approved and completion date is within the reporting period.
• Overdue: Statement end date is before the configured close deadline and Status is not Approved.

The workbook template should use named cells for:

• pReconID
• pFuzzyThreshold, initially 0.88 for the representative design
• pFuzzyMargin, initially 0.05
• pReferenceDateToleranceDays, initially 3
• pFuzzyDateToleranceDays, initially 5

These values are policy assumptions, not universal thresholds. They should be tested against historical records. A lower fuzzy threshold increases candidate volume and false-suggestion risk.

The tblRunControl table contains one row with:

• ReconID
• AccountKey
• StatementStartDate
• StatementEndDate
• BankEndingBalance
• QBOEndingBalance
• PreparedBy
• PreparedAt

Query output is loaded to two Excel tables:

• tblReconciliationOutput, one row per matched or unmatched transaction result
• tblRunSummaryOutput, one row containing readiness and control totals

Users must not type decisions directly into either output table because refresh replaces query output. Decisions belong in tblReviewDecisions, linked by stable bank and ledger row IDs.

Step 4: Connect the Tools

For source-to-canonical mapping, the implementation records the actual exported heading beside its canonical destination. For example, a bank’s posted-date column maps to TransactionDate, while a QuickBooks debit and credit pair maps to one signed Amount. Mapping is tested using known deposits, payments, reversals, and zero-value lines.

If a source file changes its headings, Power Query produces a missing-column error. The support owner updates the adapter only after confirming that the source meaning has not changed.

Step 5: Build the Core Automation

Automation 1: Create the reconciliation run

• Trigger: A SharePoint Reconciliation Runs item is created.
• Conditions: ReconID, account, dates, preparer, approver, and balances are present; no item has the same ReconID.
• Actions: Create the run folders, copy the Excel template, retrieve file metadata, update the run item, and notify the preparer.
• Fields updated: WorkbookIdentifier, WorkbookLink, Status, AutomationStatus, LastAutomationRun, and ErrorMessage.
• Notification: Send the preparer the run ID, workbook link, and source-folder link.
• Exception: On failure, set AutomationStatus to Failed and Status to Automation Failed.

The folder sequence is:

Finance Reconciliations/
  2026/
    05/
      REC-2026-05-OPERATING/
        Source/
        Support/
        Working/
        Approved/

Automation 2: Standardize and match transactions

• Trigger: The preparer selects Refresh All in desktop Excel.
• Conditions: Required tables and named parameters exist; row IDs are unique; values can be converted to required data types.
• Actions: Normalize text, convert amounts to cents, create stable IDs, perform staged matching, merge decisions, and calculate summary controls.
• Fields updated: MatchMethod, Similarity, Status, ExceptionType, adjusted balance, difference, and automation readiness.
• Notification: No automatic message is sent during local refresh. The visible output and query errors inform the preparer.
• Exception: Invalid schemas, duplicate IDs, or conversion errors stop the refresh.

Matching occurs in this exact order:

1. Unique exact account, transaction date, and amount.
2. Unique normalized reference, account, exact amount, and date tolerance.
3. Controlled fuzzy description suggestion among remaining rows with the same account, exact amount, and narrow date tolerance.
4. Human confirmation of an eligible fuzzy pair.
5. Manual classification of unmatched ledger timing items or documented bank errors.

Once a row is accepted at an earlier stage, it is removed from later candidate pools. This prevents a reference or fuzzy candidate from consuming an already matched transaction.

Automation 3: Validate approval readiness

• Trigger: SharePoint status changes to Ready for Approval.
• Conditions: No approval request exists, the workbook identifier is present, and the run is not already approved.
• Actions: Read tblRunSummaryOutput, confirm one matching ReconID, and evaluate unresolved count, difference cents, and automation status.
• Fields updated: Status becomes Approval In Progress or Blocked; validation values and timestamps are copied to the run item.
• Notification: Blocked runs are returned to the preparer with the control values.
• Exception: Missing tables, locked files, or unreadable workbooks enter the failure scope.

Before submitting, the preparer closes the workbook. This reduces the chance of Excel connector conflicts while Power Automate reads the summary table.

Step 6: Add Approvals, Reminders, and Escalations

The primary approval is sequential. The controller reviews the source files, workbook, control totals, exception decisions, and preparer evidence.

1. Power Automate sets Status to Approval In Progress.
2. It creates the primary approval assigned to the run’s Approver email address.
3. It stores the returned approval identifier immediately.
4. If the controller rejects, the flow records comments, changes Status to Rejected, and returns ownership to the preparer.
5. If the controller approves, the flow evaluates the secondary-approval policy.
6. A finance director approval is required when the run contains a documented bank error, an explicit policy override, or another organization-defined high-risk condition.
7. Only after all required approvals succeed does the flow set Status to Approved.

For a representative policy, reminders are sent after two business days and escalated after four business days. Each business should replace these timings with its financial-close calendar.

A separate scheduled Power Automate flow runs each morning and finds items where Status is Approval In Progress and the approval due date has passed. It sends a reminder without creating a second approval.

Unavailable approvers are handled through a documented delegation field. Before submission, the controller or finance administrator changes the approver to an authorized delegate. If an approval is already active, the support owner cancels or terminates the existing run according to policy, clears the approval request ID with an audit note, assigns the delegate, and resubmits. The process must not create concurrent approvals for the same ReconID.

Approval evidence includes:

• ReconID
• Workbook link and approved-copy link
• Summary control values
• Approval request identifier
• Approver identity
• Outcome
• Comments
• Response timestamp
• Flow run identifier
• Whether secondary approval was required

Step 7: Add Documents and File Management

The SharePoint library uses one folder per run. Original exports are stored in Source, explanatory documents in Support, the active workbook in Working, and final artifacts in Approved.

File names follow the ReconID naming standard. Supporting files add a short evidence type and date, for example:

REC-2026-05-OPERATING_SUPPORT_BANK-FEE_2026-05-31.pdf
REC-2026-05-OPERATING_APPROVAL_EVIDENCE.json
REC-2026-05-OPERATING_FINAL.xlsx

SharePoint version history is enabled for the working and approved areas. Users replace source files only through a documented correction process. When an export must be replaced, the original remains available through version history or is renamed with a superseded suffix.

Power Automate checks that expected source evidence exists before approval. At minimum, the bank export, bank statement, QuickBooks export, working workbook, and any cited exception support must be available.

Large files and unsupported formats are kept outside Excel tables and linked from the run. If a file upload fails, the run remains in Awaiting Files or Blocked. The preparer receives the failed file name and the destination folder but not sensitive content in the notification.

Retention follows the company’s financial-record policy and applicable legal requirements. The implementation does not assume a universal retention period.

Step 8: Add Reporting and Operational Views

The workbook provides these filtered views:

• New unmatched bank transactions
• Unmatched ledger transactions
• Eligible fuzzy suggestions awaiting confirmation
• Invalid review decisions
• Timing differences
• Documented bank errors
• Items awaiting supporting evidence
• Items by exception owner
• Recently matched transactions
• Match counts by method
• Items with repeated amounts
• Automation readiness and control difference

The SharePoint list provides run-level views for overdue reconciliations, blocked submissions, pending approvals, rejections, and automation failures.

Calculated reporting measures include:

• Bank transaction count
• Ledger transaction count
• Exact-match count
• Reference-match count
• Human-confirmed fuzzy count
• Unresolved exception count
• Accepted timing count and net amount
• Documented bank-error count and net amount
• Adjusted bank balance
• QuickBooks ending balance
• Difference in cents
• Elapsed days from statement end to approval

Excel PivotTables can summarize the output for the current run. For cross-period reporting, approved summary rows can be copied to a protected history list or imported into Power BI. The controller owns metric definitions, while the automation support owner maintains refresh and connection health.

Step 9: Add Security and Governance Controls

• Use least-privilege SharePoint groups rather than granting broad site access.
• Do not store full bank account numbers in ReconID, file names, notifications, prompts, or reporting views.
• Restrict source and support folders to authorized finance, audit, and administrative users.
• Disable anonymous sharing and review organization-wide link settings.
• Store flow connections under a controlled identity with a documented backup owner.
• Do not place passwords, API keys, or bank credentials in workbooks or flow actions.
• Review SharePoint and Power Automate activity logs when investigating changes or failures.
• Remove former employees from finance groups, flow ownership, approval assignments, and QuickBooks access promptly.
• Preserve original exports and workbook versions as audit evidence.
• Require human approval for fuzzy matches, bank errors, accounting entries, and final sign-off.
• Use masked sample data in development and testing.
• Prevent approved runs from being overwritten without a reopen process.
• Document threshold, tolerance, and mapping changes with an effective date and approver.

If AI is added, only an organization-approved service may receive data. Prompts must exclude full account numbers, credentials, personal banking information, card data, and unrelated personally identifiable information.

Step 10: Deploy and Test

1. Build the SharePoint list, document structure, workbook, queries, and flows in a test area.
2. Load at least three historical reconciliations using masked data.
3. Include duplicate amounts, date differences, blank references, reversals, deposits, checks, fees, and outstanding transactions.
4. Compare automated results with the previously approved reconciliations.
5. Review every false candidate and tune the reference and fuzzy tolerances.
6. Complete user acceptance testing with the senior accountant and controller.
7. Pilot one account for one close period while preserving the prior process as a rollback option.
8. After the pilot, activate the remaining accounts in phases.
9. Publish a short operating guide covering exports, refresh, exception decisions, approval submission, and recovery.
10. Assign a finance process owner and a technical backup owner.
11. Monitor every run during the first two close cycles.
12. Keep the previous approved workbook template available until the new process has completed the agreed stabilization period.

Rollback consists of pausing the approval flows, returning the SharePoint status to a controlled manual mode, and completing the reconciliation using the retained source exports and prior approved method. No accounting data is lost because the implementation does not modify QuickBooks Online.

Code and Configuration

The core matching logic is implemented in Power Query M. The code below assumes that the workbook already contains the named parameters and four Excel tables described earlier.

In desktop Excel, open the workbook, select the data-query area, open the Power Query editor, create a blank query, open its advanced editor, and paste the first query. Name it qReconciliationResults.

Replace the parameter values in the named cells rather than editing thresholds directly in the query. The code requests access only to the current workbook. SharePoint permissions are applied when the workbook itself is opened or when Power Automate reads it.

Power Query matching query

let
    ReconID = Text.Trim(Text.From(
        Excel.CurrentWorkbook(){[Name="pReconID"]}[Content]{0}[Column1]
    )),
    FuzzyThreshold = Number.From(
        Excel.CurrentWorkbook(){[Name="pFuzzyThreshold"]}[Content]{0}[Column1]
    ),
    FuzzyMargin = Number.From(
        Excel.CurrentWorkbook(){[Name="pFuzzyMargin"]}[Content]{0}[Column1]
    ),
    ReferenceDateToleranceDays = Int64.From(
        Excel.CurrentWorkbook(){[Name="pReferenceDateToleranceDays"]}[Content]{0}[Column1]
    ),
    FuzzyDateToleranceDays = Int64.From(
        Excel.CurrentWorkbook(){[Name="pFuzzyDateToleranceDays"]}[Content]{0}[Column1]
    ),

    AsText = (value as any) as text =>
        if value = null then "" else Text.Trim(Text.From(value)),

    NormalizeReference = (value as any) as text =>
        Text.Select(Text.Upper(AsText(value)), {"A".."Z", "0".."9"}),

    NormalizeDescription = (value as any) as text =>
        let
            UpperText = Text.Upper(AsText(value)),
            Kept = Text.Select(UpperText, {"A".."Z", "0".."9", " "}),
            Parts = List.Select(Text.SplitAny(Kept, " "), each _ <> ""),
            StopWords = {"THE", "INC", "LLC", "LTD", "LIMITED", "CO", "COMPANY"},
            UsefulParts = List.Select(Parts, each not List.Contains(StopWords, _))
        in
            Text.Combine(UsefulParts, " "),

    ToCents = (value as any) as number =>
        Int64.From(Number.Round(Number.From(value) * 100, 0, RoundingMode.AwayFromZero)),

    BankRequired = {
        "ReconID", "AccountKey", "TransactionDate", "Amount", "Description",
        "Reference", "BankTransactionID", "SourceFileName", "SourceRow"
    },
    LedgerRequired = {
        "ReconID", "AccountKey", "TransactionDate", "Amount", "Description",
        "Reference", "QBOTransactionID", "TransactionType",
        "SourceFileName", "SourceRow"
    },
    ReviewRequired = {
        "ReconID", "BankRowID", "LedgerRowID", "Decision", "ExceptionType",
        "Notes", "ReviewedBy", "ReviewedAt"
    },

    BankSource = Excel.CurrentWorkbook(){[Name="tblBankImport"]}[Content],
    BankSelected = Table.SelectColumns(BankSource, BankRequired, MissingField.Error),
    BankRun = Table.SelectRows(BankSelected, each AsText([ReconID]) = ReconID),
    BankTyped = Table.TransformColumns(
        BankRun,
        {
            {"ReconID", each AsText(_), type text},
            {"AccountKey", each Text.Upper(AsText(_)), type text},
            {"TransactionDate", each Date.From(_), type date},
            {"Amount", each Number.From(_), type number},
            {"Description", each AsText(_), type text},
            {"Reference", each AsText(_), type text},
            {"BankTransactionID", each AsText(_), type text},
            {"SourceFileName", each AsText(_), type text},
            {"SourceRow", each Int64.From(_), Int64.Type}
        }
    ),
    BankCents = Table.AddColumn(
        BankTyped, "AmountCents", each ToCents([Amount]), Int64.Type
    ),
    BankDescription = Table.AddColumn(
        BankCents, "DescriptionNorm", each NormalizeDescription([Description]), type text
    ),
    BankReference = Table.AddColumn(
        BankDescription, "ReferenceNorm", each NormalizeReference([Reference]), type text
    ),
    BankID = Table.AddColumn(
        BankReference,
        "BankRowID",
        each
            if [BankTransactionID] <> "" then
                [AccountKey] & "|B|" & [BankTransactionID]
            else
                [AccountKey] & "|B|" & [SourceFileName] & "|" & Text.From([SourceRow]),
        type text
    ),
    BankKeyed = Table.AddColumn(
        BankID,
        "ExactKey",
        each [AccountKey] & "|" &
            Date.ToText([TransactionDate], "yyyyMMdd") & "|" &
            Text.From([AmountCents]),
        type text
    ),
    BankRefKeyed = Table.AddColumn(
        BankKeyed,
        "ReferenceKey",
        each [AccountKey] & "|" & Text.From([AmountCents]) & "|" & [ReferenceNorm],
        type text
    ),
    BankDuplicateIDs = Table.SelectRows(
        Table.Group(BankRefKeyed, {"BankRowID"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
        each [Count] > 1
    ),
    Bank = if Table.RowCount(BankDuplicateIDs) > 0
        then error "Duplicate BankRowID values were found. Correct the source IDs or source-row values."
        else BankRefKeyed,

    LedgerSource = Excel.CurrentWorkbook(){[Name="tblLedgerImport"]}[Content],
    LedgerSelected = Table.SelectColumns(LedgerSource, LedgerRequired, MissingField.Error),
    LedgerRun = Table.SelectRows(LedgerSelected, each AsText([ReconID]) = ReconID),
    LedgerTyped = Table.TransformColumns(
        LedgerRun,
        {
            {"ReconID", each AsText(_), type text},
            {"AccountKey", each Text.Upper(AsText(_)), type text},
            {"TransactionDate", each Date.From(_), type date},
            {"Amount", each Number.From(_), type number},
            {"Description", each AsText(_), type text},
            {"Reference", each AsText(_), type text},
            {"QBOTransactionID", each AsText(_), type text},
            {"TransactionType", each AsText(_), type text},
            {"SourceFileName", each AsText(_), type text},
            {"SourceRow", each Int64.From(_), Int64.Type}
        }
    ),
    LedgerCents = Table.AddColumn(
        LedgerTyped, "AmountCents", each ToCents([Amount]), Int64.Type
    ),
    LedgerDescription = Table.AddColumn(
        LedgerCents, "DescriptionNorm", each NormalizeDescription([Description]), type text
    ),
    LedgerReference = Table.AddColumn(
        LedgerDescription, "ReferenceNorm", each NormalizeReference([Reference]), type text
    ),
    LedgerID = Table.AddColumn(
        LedgerReference,
        "LedgerRowID",
        each
            if [QBOTransactionID] <> "" then
                [AccountKey] & "|L|" & [QBOTransactionID]
            else
                [AccountKey] & "|L|" & [SourceFileName] & "|" & Text.From([SourceRow]),
        type text
    ),
    LedgerKeyed = Table.AddColumn(
        LedgerID,
        "ExactKey",
        each [AccountKey] & "|" &
            Date.ToText([TransactionDate], "yyyyMMdd") & "|" &
            Text.From([AmountCents]),
        type text
    ),
    LedgerRefKeyed = Table.AddColumn(
        LedgerKeyed,
        "ReferenceKey",
        each [AccountKey] & "|" & Text.From([AmountCents]) & "|" & [ReferenceNorm],
        type text
    ),
    LedgerDuplicateIDs = Table.SelectRows(
        Table.Group(LedgerRefKeyed, {"LedgerRowID"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
        each [Count] > 1
    ),
    Ledger = if Table.RowCount(LedgerDuplicateIDs) > 0
        then error "Duplicate LedgerRowID values were found. Correct the source IDs or source-row values."
        else LedgerRefKeyed,

    BankExactCounts = Table.Group(
        Bank, {"ExactKey"}, {{"BankKeyCount", each Table.RowCount(_), Int64.Type}}
    ),
    LedgerExactCounts = Table.Group(
        Ledger, {"ExactKey"}, {{"LedgerKeyCount", each Table.RowCount(_), Int64.Type}}
    ),
    ExactJoin0 = Table.NestedJoin(
        Bank, {"ExactKey"}, Ledger, {"ExactKey"}, "LedgerMatch", JoinKind.Inner
    ),
    ExactJoin1 = Table.ExpandTableColumn(
        ExactJoin0, "LedgerMatch", {"LedgerRowID"}, {"LedgerRowID"}
    ),
    ExactJoin2 = Table.NestedJoin(
        ExactJoin1, {"ExactKey"}, BankExactCounts, {"ExactKey"}, "BankCounts", JoinKind.LeftOuter
    ),
    ExactJoin3 = Table.ExpandTableColumn(
        ExactJoin2, "BankCounts", {"BankKeyCount"}, {"BankKeyCount"}
    ),
    ExactJoin4 = Table.NestedJoin(
        ExactJoin3, {"ExactKey"}, LedgerExactCounts, {"ExactKey"}, "LedgerCounts", JoinKind.LeftOuter
    ),
    ExactJoin5 = Table.ExpandTableColumn(
        ExactJoin4, "LedgerCounts", {"LedgerKeyCount"}, {"LedgerKeyCount"}
    ),
    ExactMatches = Table.SelectRows(
        ExactJoin5, each [BankKeyCount] = 1 and [LedgerKeyCount] = 1
    ),
    ExactOut0 = Table.SelectColumns(ExactMatches, {"BankRowID", "LedgerRowID"}),
    ExactOut1 = Table.AddColumn(ExactOut0, "MatchMethod", each "Exact", type text),
    ExactOut = Table.AddColumn(ExactOut1, "MatchConfidence", each 1.0, type number),

    BankAfterExact = Table.SelectRows(
        Bank, each not List.Contains(ExactOut[BankRowID], [BankRowID])
    ),
    LedgerAfterExact = Table.SelectRows(
        Ledger, each not List.Contains(ExactOut[LedgerRowID], [LedgerRowID])
    ),

    RefBankEligible = Table.SelectRows(BankAfterExact, each [ReferenceNorm] <> ""),
    RefLedgerEligible = Table.SelectRows(LedgerAfterExact, each [ReferenceNorm] <> ""),
    RefJoin0 = Table.NestedJoin(
        RefBankEligible,
        {"ReferenceKey"},
        RefLedgerEligible,
        {"ReferenceKey"},
        "LedgerMatch",
        JoinKind.Inner
    ),
    RefJoin1 = Table.ExpandTableColumn(
        RefJoin0,
        "LedgerMatch",
        {"LedgerRowID", "TransactionDate"},
        {"LedgerRowID", "LedgerDate"}
    ),
    RefJoin2 = Table.AddColumn(
        RefJoin1,
        "DateGapDays",
        each Number.Abs(Duration.Days([LedgerDate] - [TransactionDate])),
        Int64.Type
    ),
    RefCandidates = Table.SelectRows(
        RefJoin2, each [DateGapDays] <= ReferenceDateToleranceDays
    ),
    RefBankCounts = Table.Group(
        RefCandidates, {"BankRowID"}, {{"BankCandidateCount", each Table.RowCount(_), Int64.Type}}
    ),
    RefLedgerCounts = Table.Group(
        RefCandidates, {"LedgerRowID"}, {{"LedgerCandidateCount", each Table.RowCount(_), Int64.Type}}
    ),
    RefJoin3 = Table.NestedJoin(
        RefCandidates, {"BankRowID"}, RefBankCounts, {"BankRowID"}, "BankCounts", JoinKind.LeftOuter
    ),
    RefJoin4 = Table.ExpandTableColumn(
        RefJoin3, "BankCounts", {"BankCandidateCount"}, {"BankCandidateCount"}
    ),
    RefJoin5 = Table.NestedJoin(
        RefJoin4, {"LedgerRowID"}, RefLedgerCounts, {"LedgerRowID"}, "LedgerCounts", JoinKind.LeftOuter
    ),
    RefJoin6 = Table.ExpandTableColumn(
        RefJoin5, "LedgerCounts", {"LedgerCandidateCount"}, {"LedgerCandidateCount"}
    ),
    ReferenceMatches = Table.SelectRows(
        RefJoin6, each [BankCandidateCount] = 1 and [LedgerCandidateCount] = 1
    ),
    ReferenceOut0 = Table.SelectColumns(ReferenceMatches, {"BankRowID", "LedgerRowID"}),
    ReferenceOut1 = Table.AddColumn(ReferenceOut0, "MatchMethod", each "Reference", type text),
    ReferenceOut = Table.AddColumn(ReferenceOut1, "MatchConfidence", each 1.0, type number),

    AutoMatches = Table.Combine({ExactOut, ReferenceOut}),

    BankAfterReference = Table.SelectRows(
        BankAfterExact, each not List.Contains(ReferenceOut[BankRowID], [BankRowID])
    ),
    LedgerAfterReference = Table.SelectRows(
        LedgerAfterExact, each not List.Contains(ReferenceOut[LedgerRowID], [LedgerRowID])
    ),

    EmptyFuzzy = #table(
        type table [
            BankRowID = text,
            SuggestedLedgerRowID = text,
            Similarity = number,
            DateGapDays = number
        ],
        {}
    ),

    FuzzyForBank = (bankRow as record) as table =>
        let
            Candidates = Table.SelectRows(
                LedgerAfterReference,
                each
                    [AccountKey] = bankRow[AccountKey] and
                    [AmountCents] = bankRow[AmountCents] and
                    Number.Abs(Duration.Days([TransactionDate] - bankRow[TransactionDate]))
                        <= FuzzyDateToleranceDays and
                    [DescriptionNorm] <> ""
            ),
            CandidateColumns = Table.SelectColumns(
                Candidates, {"LedgerRowID", "TransactionDate", "DescriptionNorm"}
            ),
            RenamedCandidates = Table.RenameColumns(
                CandidateColumns,
                {
                    {"LedgerRowID", "SuggestedLedgerRowID"},
                    {"TransactionDate", "LedgerDate"},
                    {"DescriptionNorm", "CandidateText"}
                }
            ),
            Search = #table(
                type table [SearchText = text],
                {{bankRow[DescriptionNorm]}}
            ),
            Joined = if bankRow[DescriptionNorm] = "" or Table.IsEmpty(RenamedCandidates)
                then null
                else Table.FuzzyNestedJoin(
                    Search,
                    {"SearchText"},
                    RenamedCandidates,
                    {"CandidateText"},
                    "Matches",
                    JoinKind.LeftOuter,
                    [
                        IgnoreCase = true,
                        IgnoreSpace = true,
                        Threshold = FuzzyThreshold,
                        NumberOfMatches = 2,
                        SimilarityColumnName = "Similarity"
                    ]
                ),
            Expanded = if Joined = null
                then EmptyFuzzy
                else Table.ExpandTableColumn(
                    Joined,
                    "Matches",
                    {"SuggestedLedgerRowID", "LedgerDate", "Similarity"},
                    {"SuggestedLedgerRowID", "LedgerDate", "Similarity"}
                ),
            Valid = Table.SelectRows(
                Expanded,
                each [SuggestedLedgerRowID] <> null and [Similarity] <> null
            ),
            WithBankID = Table.AddColumn(
                Valid, "BankRowID", each bankRow[BankRowID], type text
            ),
            WithDateGap = Table.AddColumn(
                WithBankID,
                "DateGapDays",
                each Number.Abs(Duration.Days([LedgerDate] - bankRow[TransactionDate])),
                Int64.Type
            ),
            Selected = Table.SelectColumns(
                WithDateGap,
                {"BankRowID", "SuggestedLedgerRowID", "Similarity", "DateGapDays"}
            )
        in
            Selected,

    FuzzyNested = Table.AddColumn(
        BankAfterReference, "FuzzyCandidates", each FuzzyForBank(_)
    ),
    FuzzyCandidateTables = FuzzyNested[FuzzyCandidates],
    FuzzyCandidates = if List.Count(FuzzyCandidateTables) = 0
        then EmptyFuzzy
        else Table.Combine(FuzzyCandidateTables),

    EmptyFuzzyTop = #table(
        type table [
            BankRowID = text,
            SuggestedLedgerRowID = text,
            Similarity = number,
            RunnerUpSimilarity = number,
            DateGapDays = number
        ],
        {}
    ),
    FuzzyTop0 = if Table.IsEmpty(FuzzyCandidates)
        then EmptyFuzzyTop
        else Table.Group(
            FuzzyCandidates,
            {"BankRowID"},
            {
                {
                    "TopRecord",
                    each
                        let
                            Sorted = Table.Sort(_, {{"Similarity", Order.Descending}}),
                            First = Sorted{0},
                            RunnerUp = if Table.RowCount(Sorted) > 1
                                then Sorted{1}[Similarity]
                                else null
                        in
                            [
                                SuggestedLedgerRowID = First[SuggestedLedgerRowID],
                                Similarity = First[Similarity],
                                RunnerUpSimilarity = RunnerUp,
                                DateGapDays = First[DateGapDays]
                            ],
                    type record
                }
            }
        ),
    FuzzyTop1 = if Table.IsEmpty(FuzzyTop0)
        then EmptyFuzzyTop
        else Table.ExpandRecordColumn(
            FuzzyTop0,
            "TopRecord",
            {"SuggestedLedgerRowID", "Similarity", "RunnerUpSimilarity", "DateGapDays"},
            {"SuggestedLedgerRowID", "Similarity", "RunnerUpSimilarity", "DateGapDays"}
        ),
    SuggestedLedgerCounts = Table.Group(
        FuzzyTop1,
        {"SuggestedLedgerRowID"},
        {{"SuggestedLedgerCount", each Table.RowCount(_), Int64.Type}}
    ),
    FuzzyTop2 = Table.NestedJoin(
        FuzzyTop1,
        {"SuggestedLedgerRowID"},
        SuggestedLedgerCounts,
        {"SuggestedLedgerRowID"},
        "SuggestionCounts",
        JoinKind.LeftOuter
    ),
    FuzzyTop3 = Table.ExpandTableColumn(
        FuzzyTop2,
        "SuggestionCounts",
        {"SuggestedLedgerCount"},
        {"SuggestedLedgerCount"}
    ),
    FuzzyTop = Table.AddColumn(
        FuzzyTop3,
        "SuggestedEligible",
        each
            [Similarity] >= FuzzyThreshold and
            ([RunnerUpSimilarity] = null or [Similarity] - [RunnerUpSimilarity] >= FuzzyMargin) and
            [SuggestedLedgerCount] = 1,
        type logical
    ),

    ReviewSource = Excel.CurrentWorkbook(){[Name="tblReviewDecisions"]}[Content],
    ReviewSelected = Table.SelectColumns(ReviewSource, ReviewRequired, MissingField.Error),
    ReviewRun = Table.SelectRows(ReviewSelected, each AsText([ReconID]) = ReconID),
    ReviewTyped = Table.TransformColumns(
        ReviewRun,
        {
            {"ReconID", each AsText(_), type text},
            {"BankRowID", each AsText(_), type text},
            {"LedgerRowID", each AsText(_), type text},
            {"Decision", each AsText(_), type text},
            {"ExceptionType", each AsText(_), type text},
            {"Notes", each AsText(_), type text},
            {"ReviewedBy", each AsText(_), type text},
            {"ReviewedAt", each if _ = null or AsText(_) = "" then null else DateTime.From(_), type datetime}
        }
    ),
    ActiveReviews = Table.SelectRows(ReviewTyped, each [Decision] <> ""),
    BankReviews0 = Table.SelectRows(ActiveReviews, each [BankRowID] <> ""),
    BankReviewDuplicates = Table.SelectRows(
        Table.Group(BankReviews0, {"BankRowID"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
        each [Count] > 1
    ),
    BankReviews = if Table.RowCount(BankReviewDuplicates) > 0
        then error "More than one active review decision exists for a BankRowID."
        else BankReviews0,

    AcceptedFuzzy0 = Table.SelectRows(BankReviews, each [Decision] = "Accept Fuzzy"),
    AcceptedFuzzy1 = Table.NestedJoin(
        AcceptedFuzzy0,
        {"BankRowID", "LedgerRowID"},
        FuzzyTop,
        {"BankRowID", "SuggestedLedgerRowID"},
        "Suggestion",
        JoinKind.LeftOuter
    ),
    AcceptedFuzzy2 = Table.ExpandTableColumn(
        AcceptedFuzzy1,
        "Suggestion",
        {"SuggestedEligible", "Similarity"},
        {"SuggestedEligible", "AcceptedSimilarity"}
    ),
    AcceptedLedgerCounts = Table.Group(
        AcceptedFuzzy2,
        {"LedgerRowID"},
        {{"AcceptedLedgerCount", each Table.RowCount(_), Int64.Type}}
    ),
    AcceptedFuzzy3 = Table.NestedJoin(
        AcceptedFuzzy2,
        {"LedgerRowID"},
        AcceptedLedgerCounts,
        {"LedgerRowID"},
        "AcceptedCounts",
        JoinKind.LeftOuter
    ),
    AcceptedFuzzy4 = Table.ExpandTableColumn(
        AcceptedFuzzy3,
        "AcceptedCounts",
        {"AcceptedLedgerCount"},
        {"AcceptedLedgerCount"}
    ),
    AcceptedFuzzy = Table.SelectRows(
        AcceptedFuzzy4,
        each [SuggestedEligible] = true and [AcceptedLedgerCount] = 1
    ),

    BankResult1 = Table.NestedJoin(
        Bank,
        {"BankRowID"},
        AutoMatches,
        {"BankRowID"},
        "Auto",
        JoinKind.LeftOuter
    ),
    BankResult2 = Table.ExpandTableColumn(
        BankResult1,
        "Auto",
        {"LedgerRowID", "MatchMethod", "MatchConfidence"},
        {"AutoLedgerRowID", "AutoMatchMethod", "AutoMatchConfidence"}
    ),
    BankResult3 = Table.NestedJoin(
        BankResult2,
        {"BankRowID"},
        FuzzyTop,
        {"BankRowID"},
        "Fuzzy",
        JoinKind.LeftOuter
    ),
    BankResult4 = Table.ExpandTableColumn(
        BankResult3,
        "Fuzzy",
        {"SuggestedLedgerRowID", "Similarity", "RunnerUpSimilarity", "SuggestedEligible"},
        {"SuggestedLedgerRowID", "Similarity", "RunnerUpSimilarity", "SuggestedEligible"}
    ),
    BankResult5 = Table.NestedJoin(
        BankResult4,
        {"BankRowID"},
        BankReviews,
        {"BankRowID"},
        "Review",
        JoinKind.LeftOuter
    ),
    BankResult6 = Table.ExpandTableColumn(
        BankResult5,
        "Review",
        {"LedgerRowID", "Decision", "ExceptionType", "Notes", "ReviewedBy", "ReviewedAt"},
        {"ReviewLedgerRowID", "Decision", "ReviewExceptionType", "Notes", "ReviewedBy", "ReviewedAt"}
    ),
    BankResult7 = Table.NestedJoin(
        BankResult6,
        {"BankRowID", "ReviewLedgerRowID"},
        AcceptedFuzzy,
        {"BankRowID", "LedgerRowID"},
        "Accepted",
        JoinKind.LeftOuter
    ),
    BankResult8 = Table.ExpandTableColumn(
        BankResult7,
        "Accepted",
        {"LedgerRowID", "AcceptedSimilarity"},
        {"AcceptedLedgerRowID", "AcceptedSimilarity"}
    ),
    BankResult9 = Table.AddColumn(
        BankResult8,
        "FinalLedgerRowID",
        each
            if [AutoLedgerRowID] <> null then [AutoLedgerRowID]
            else if [AcceptedLedgerRowID] <> null then [AcceptedLedgerRowID]
            else null,
        type text
    ),
    BankResult10 = Table.AddColumn(
        BankResult9,
        "Status",
        each
            if [AutoLedgerRowID] <> null then "Matched"
            else if [AcceptedLedgerRowID] <> null then "Matched - Reviewed"
            else if [Decision] = "Bank Error Documented" then "Accepted Exception"
            else if [Decision] = "Accept Fuzzy" then "Invalid Review Decision"
            else "Manual Review",
        type text
    ),
    BankResult11 = Table.AddColumn(
        BankResult10,
        "FinalMatchMethod",
        each
            if [AutoMatchMethod] <> null then [AutoMatchMethod]
            else if [AcceptedLedgerRowID] <> null then "Fuzzy - human confirmed"
            else null,
        type text
    ),
    BankResult12 = Table.AddColumn(
        BankResult11,
        "FinalConfidence",
        each
            if [AutoMatchConfidence] <> null then [AutoMatchConfidence]
            else if [AcceptedSimilarity] <> null then [AcceptedSimilarity]
            else null,
        type number
    ),
    BankResult13 = Table.NestedJoin(
        BankResult12,
        {"FinalLedgerRowID"},
        Ledger,
        {"LedgerRowID"},
        "FinalLedger",
        JoinKind.LeftOuter
    ),
    BankResult14 = Table.ExpandTableColumn(
        BankResult13,
        "FinalLedger",
        {"TransactionDate", "AmountCents", "Description"},
        {"LedgerDate", "LedgerAmountCents", "LedgerDescription"}
    ),
    BankResult15 = Table.AddColumn(BankResult14, "ResultType", each "Bank", type text),
    BankResult16 = Table.AddColumn(
        BankResult15,
        "OutputExceptionType",
        each
            if [ReviewExceptionType] <> null and [ReviewExceptionType] <> ""
                then [ReviewExceptionType]
            else if [SuggestedEligible] = true
                then "Fuzzy suggestion"
            else if [Status] = "Manual Review"
                then "Unmatched bank transaction"
            else null,
        type text
    ),
    BankOutputRenamed = Table.RenameColumns(
        BankResult16,
        {
            {"TransactionDate", "BankDate"},
            {"AmountCents", "BankAmountCents"},
            {"Description", "BankDescription"}
        }
    ),
    BankOutput = Table.SelectColumns(
        BankOutputRenamed,
        {
            "ReconID", "ResultType", "BankRowID", "FinalLedgerRowID",
            "AccountKey", "BankDate", "LedgerDate", "BankAmountCents",
            "LedgerAmountCents", "BankDescription", "LedgerDescription",
            "Reference", "FinalMatchMethod", "FinalConfidence", "Similarity",
            "SuggestedLedgerRowID", "SuggestedEligible", "Status", "Decision",
            "OutputExceptionType", "Notes", "ReviewedBy", "ReviewedAt"
        }
    ),
    BankOutputFinal = Table.RenameColumns(
        BankOutput,
        {
            {"FinalLedgerRowID", "LedgerRowID"},
            {"FinalMatchMethod", "MatchMethod"},
            {"FinalConfidence", "MatchConfidence"},
            {"OutputExceptionType", "ExceptionType"}
        }
    ),

    UsedLedgerIDs = List.RemoveNulls(
        List.Combine({AutoMatches[LedgerRowID], AcceptedFuzzy[LedgerRowID]})
    ),
    LedgerOnly0 = Table.SelectRows(
        Ledger, each not List.Contains(UsedLedgerIDs, [LedgerRowID])
    ),
    LedgerOnlyReviews = Table.SelectRows(
        ActiveReviews, each [BankRowID] = "" and [LedgerRowID] <> ""
    ),
    LedgerOnlyReviewDuplicates = Table.SelectRows(
        Table.Group(
            LedgerOnlyReviews,
            {"LedgerRowID"},
            {{"Count", each Table.RowCount(_), Int64.Type}}
        ),
        each [Count] > 1
    ),
    ValidLedgerOnlyReviews = if Table.RowCount(LedgerOnlyReviewDuplicates) > 0
        then error "More than one active ledger-only decision exists for a LedgerRowID."
        else LedgerOnlyReviews,
    LedgerOnly1 = Table.NestedJoin(
        LedgerOnly0,
        {"LedgerRowID"},
        ValidLedgerOnlyReviews,
        {"LedgerRowID"},
        "Review",
        JoinKind.LeftOuter
    ),
    LedgerOnly2 = Table.ExpandTableColumn(
        LedgerOnly1,
        "Review",
        {"Decision", "ExceptionType", "Notes", "ReviewedBy", "ReviewedAt"},
        {"Decision", "ExceptionType", "Notes", "ReviewedBy", "ReviewedAt"}
    ),
    LedgerOnly3 = Table.AddColumn(
        LedgerOnly2,
        "Status",
        each if [Decision] = "Timing Difference"
            then "Accepted Exception"
            else "Manual Review",
        type text
    ),
    LedgerOnly4 = Table.AddColumn(LedgerOnly3, "ResultType", each "Ledger", type text),
    LedgerOnly5 = Table.AddColumn(LedgerOnly4, "BankRowID", each null, type text),
    LedgerOnly6 = Table.AddColumn(LedgerOnly5, "BankDate", each null, type date),
    LedgerOnly7 = Table.AddColumn(LedgerOnly6, "BankAmountCents", each null, Int64.Type),
    LedgerOnly8 = Table.AddColumn(LedgerOnly7, "BankDescription", each null, type text),
    LedgerOnly9 = Table.AddColumn(LedgerOnly8, "MatchMethod", each null, type text),
    LedgerOnly10 = Table.AddColumn(LedgerOnly9, "MatchConfidence", each null, type number),
    LedgerOnly11 = Table.AddColumn(LedgerOnly10, "Similarity", each null, type number),
    LedgerOnly12 = Table.AddColumn(
        LedgerOnly11, "SuggestedLedgerRowID", each null, type text
    ),
    LedgerOnly13 = Table.AddColumn(
        LedgerOnly12, "SuggestedEligible", each false, type logical
    ),
    LedgerOutputRenamed = Table.RenameColumns(
        LedgerOnly13,
        {
            {"TransactionDate", "LedgerDate"},
            {"AmountCents", "LedgerAmountCents"},
            {"Description", "LedgerDescription"}
        }
    ),
    LedgerOutput = Table.SelectColumns(
        LedgerOutputRenamed,
        {
            "ReconID", "ResultType", "BankRowID", "LedgerRowID",
            "AccountKey", "BankDate", "LedgerDate", "BankAmountCents",
            "LedgerAmountCents", "BankDescription", "LedgerDescription",
            "Reference", "MatchMethod", "MatchConfidence", "Similarity",
            "SuggestedLedgerRowID", "SuggestedEligible", "Status", "Decision",
            "ExceptionType", "Notes", "ReviewedBy", "ReviewedAt"
        }
    ),

    Combined = Table.Combine({BankOutputFinal, LedgerOutput}),
    Sorted = Table.Sort(
        Combined,
        {
            {"Status", Order.Ascending},
            {"BankDate", Order.Ascending},
            {"LedgerDate", Order.Ascending}
        }
    )
in
    Sorted

Load this query to an Excel table and name the resulting table tblReconciliationOutput.

The query intentionally fails when required columns or row identifiers are invalid. Inspect the error in the Power Query editor. The most likely issues are changed source headings, text in an amount field, invalid dates, duplicated source IDs, or duplicate review decisions.

Power Query summary query

Create a second blank query named qRunSummary and paste this code. It references qReconciliationResults and the run-control table.

let
    Results = qReconciliationResults,
    ReconID = Text.Trim(Text.From(
        Excel.CurrentWorkbook(){[Name="pReconID"]}[Content]{0}[Column1]
    )),

    ToCents = (value as any) as number =>
        Int64.From(Number.Round(Number.From(value) * 100, 0, RoundingMode.AwayFromZero)),

    ControlSource = Excel.CurrentWorkbook(){[Name="tblRunControl"]}[Content],
    ControlRun = Table.SelectRows(
        ControlSource,
        each Text.Trim(Text.From([ReconID])) = ReconID
    ),
    CheckedControl = if Table.RowCount(ControlRun) <> 1
        then error "tblRunControl must contain exactly one row for the active ReconID."
        else ControlRun,
    Control = CheckedControl{0},

    BankEndingBalanceCents = ToCents(Control[BankEndingBalance]),
    QBOEndingBalanceCents = ToCents(Control[QBOEndingBalance]),

    TimingRows = Table.SelectRows(
        Results,
        each
            [ResultType] = "Ledger" and
            [Status] = "Accepted Exception" and
            [Decision] = "Timing Difference"
    ),
    TimingNetCents = List.Sum(List.RemoveNulls(TimingRows[LedgerAmountCents])),

    BankErrorRows = Table.SelectRows(
        Results,
        each
            [ResultType] = "Bank" and
            [Status] = "Accepted Exception" and
            [Decision] = "Bank Error Documented"
    ),
    BankErrorNetCents = List.Sum(List.RemoveNulls(BankErrorRows[BankAmountCents])),

    AdjustedBankBalanceCents =
        BankEndingBalanceCents + TimingNetCents - BankErrorNetCents,
    DifferenceCents = AdjustedBankBalanceCents - QBOEndingBalanceCents,

    UnresolvedRows = Table.SelectRows(
        Results,
        each [Status] = "Manual Review" or [Status] = "Invalid Review Decision"
    ),
    UnresolvedCount = Table.RowCount(UnresolvedRows),
    MatchedCount = Table.RowCount(
        Table.SelectRows(Results, each Text.StartsWith([Status], "Matched"))
    ),
    ExactMatchCount = Table.RowCount(
        Table.SelectRows(Results, each [MatchMethod] = "Exact")
    ),
    ReferenceMatchCount = Table.RowCount(
        Table.SelectRows(Results, each [MatchMethod] = "Reference")
    ),
    FuzzyConfirmedCount = Table.RowCount(
        Table.SelectRows(Results, each [MatchMethod] = "Fuzzy - human confirmed")
    ),
    BankErrorCount = Table.RowCount(BankErrorRows),
    TimingDifferenceCount = Table.RowCount(TimingRows),

    AutomationStatus = if UnresolvedCount = 0 and DifferenceCents = 0
        then "Ready"
        else "Blocked",

    Output = Table.FromRecords(
        {
            [
                ReconID = ReconID,
                BankEndingBalanceCents = BankEndingBalanceCents,
                TimingNetCents = TimingNetCents,
                BankErrorNetCents = BankErrorNetCents,
                AdjustedBankBalanceCents = AdjustedBankBalanceCents,
                QBOEndingBalanceCents = QBOEndingBalanceCents,
                DifferenceCents = DifferenceCents,
                UnresolvedCount = UnresolvedCount,
                MatchedCount = MatchedCount,
                ExactMatchCount = ExactMatchCount,
                ReferenceMatchCount = ReferenceMatchCount,
                FuzzyConfirmedCount = FuzzyConfirmedCount,
                TimingDifferenceCount = TimingDifferenceCount,
                BankErrorCount = BankErrorCount,
                AutomationStatus = AutomationStatus,
                LastCalculatedAt = DateTimeZone.UtcNow()
            ]
        }
    )
in
    Output

Load this query to a one-row Excel table named tblRunSummaryOutput. Test it by changing one ending balance by one cent. The automation status should become Blocked.

Power Automate approval configuration

Create an automated cloud flow triggered by a modified SharePoint list item. Add a trigger condition so ordinary flow-written updates do not start duplicate approval runs. The exact internal column name may vary.

@and(
  equals(triggerBody()?['Status']?['Value'], 'Ready for Approval'),
  empty(triggerBody()?['ApprovalRequestID'])
)

Configure trigger concurrency to one run at a time for the same process if the tenant’s flow settings permit it. The flow uses these scopes:

1. Initialize: Set ReconID, workbook identifier, preparer email, approver email, and run item ID variables.
2. Try: Read and validate the workbook summary, create approvals, process outcomes, and retain evidence.
3. Catch: Run after Try has failed, timed out, or been skipped due to failure. Update the run to Automation Failed and notify support.
4. Finally: Run after both success and failure paths. Update LastAutomationRun and write a sanitized run reference.

Use the Excel Online action that lists rows from a table, with the file identifier stored in the SharePoint run and table name tblRunSummaryOutput. Extract the single summary record with:

first(body('List_rows_present_in_a_table')?['value'])

Create variables using these expressions:

int(first(body('List_rows_present_in_a_table')?['value'])?['UnresolvedCount'])

int(first(body('List_rows_present_in_a_table')?['value'])?['DifferenceCents'])

first(body('List_rows_present_in_a_table')?['value'])?['AutomationStatus']

first(body('List_rows_present_in_a_table')?['value'])?['ReconID']

The validation condition is:

@and(
  equals(variables('SummaryReconID'), variables('ReconID')),
  equals(variables('AutomationStatus'), 'Ready'),
  equals(variables('UnresolvedCount'), 0),
  equals(variables('DifferenceCents'), 0)
)

If false, update the run to Blocked and notify the preparer. If true, set Approval In Progress and start the primary approval.

Store the approval output using dynamic values supplied by the approval action. Interface labels can vary, but the underlying values required are outcome, response collection, responder identity, response date, comments, and approval identifier.

Configure transient SharePoint and Excel actions with a limited retry policy for temporary server errors and throttling. Do not retry validation failures or missing-table errors as though they were transient. The Catch scope records those as manual recovery items.

After final approval, create an evidence file containing a JSON object similar to this:

{
  "reconId": "REC-2026-05-OPERATING",
  "workbookUrl": "YOUR_WORKBOOK_URL",
  "approvedCopyUrl": "YOUR_APPROVED_COPY_URL",
  "differenceCents": 0,
  "unresolvedCount": 0,
  "primaryApprovalId": "YOUR_APPROVAL_ID",
  "primaryApprover": "YOUR_EMAIL_ADDRESS",
  "primaryOutcome": "Approve",
  "primaryComments": "Reviewed source files, timing items, and control totals.",
  "primaryResponseDateUtc": "2026-06-04T15:42:18Z",
  "secondaryApprovalRequired": false,
  "flowRunId": "YOUR_FLOW_RUN_ID",
  "evidenceCreatedUtc": "2026-06-04T15:43:02Z"
}

Test the flow with a copied workbook and test users. Confirm that it blocks a one-cent difference, an unresolved item, an incorrect ReconID, a missing table, and a duplicate approval request.

Failure Handling and Operational Reliability

Idempotency means the same event can be processed again without creating a second business result. The implementation achieves this with a unique ReconID, deterministic transaction row IDs, one active approval request ID, status-based trigger conditions, and evidence file names based on ReconID.

The SharePoint run item functions as the operational failure queue. Items with AutomationStatus equal to Failed are reviewed daily during close. The ErrorMessage field contains a sanitized description, while full connector diagnostics remain in restricted flow run history.

Reconciliation between systems occurs at two levels:

• Power Query confirms that every imported bank and ledger row is represented in an output status.
• Power Automate confirms that the summary ReconID equals the SharePoint ReconID before approval.

Manual recovery never involves editing calculated query output. Users correct inputs, decisions, permissions, or connections, then rerun the controlled process.

A Complete Example

For May 2026, the senior accountant creates the operating-account run. Power Automate generates REC-2026-05-OPERATING, creates the SharePoint folders, copies the workbook template, and updates the list item with the workbook identifier.

The bank export contains 438 transactions. One row is:

ReconID: REC-2026-05-OPERATING
AccountKey: OPERATING
TransactionDate: 2026-05-14
Amount: -1842.60
Description: ACH PMT HARBOR SUPPLY INV 88421
Reference: 88421
BankTransactionID: BANK-580274
SourceFileName: REC-2026-05-OPERATING_BANK_ORIGINAL.csv
SourceRow: 147

The QuickBooks export contains 421 ledger transactions. The related row is:

ReconID: REC-2026-05-OPERATING
AccountKey: OPERATING
TransactionDate: 2026-05-13
Amount: -1842.60
Description: Harbor Supply Co Bill Payment
Reference: INV-88421
QBOTransactionID: LEDGER-713984
TransactionType: Bill Payment
SourceFileName: REC-2026-05-OPERATING_QBO_ORIGINAL.xlsx
SourceRow: 132

Power Query creates these row identifiers:

• BankRowID: OPERATING|B|BANK-580274
• LedgerRowID: OPERATING|L|LEDGER-713984

The exact match fails because the transaction dates differ by one day. Reference normalization converts 88421 and INV-88421 to different normalized values if the prefix remains significant. During implementation, Harborstone’s approved reference adapter strips the known INV prefix before the general query, producing 88421 on both sides.

The reference stage then evaluates:

• Account keys are equal.
• Signed amounts are both negative 184,260 cents.
• Normalized references are equal.
• Date difference is one day, within the three-day tolerance.
• Only one bank row and one ledger row satisfy the candidate rule.

The output classifies the pair as Matched with method Reference. No human fuzzy decision is needed.

A separate ledger payment of negative 326.40 has not cleared the bank by May 31. It appears as a ledger-only exception. The senior accountant checks the payment support and records:

Decision: Timing Difference
ExceptionType: Outstanding payment
Notes: Payment issued May 30 and not present on the May bank statement.
ReviewedBy: senior.accountant@YOUR_DOMAIN
ReviewedAt: 2026-06-03 14:20

Power Query adds negative 32,640 cents to the bank ending balance as an outstanding ledger item. The adjusted bank balance then equals the QuickBooks ending balance, and no unresolved transactions remain.

The preparer changes the run to Ready for Approval. Power Automate reads the summary:

{
  "ReconID": "REC-2026-05-OPERATING",
  "DifferenceCents": 0,
  "UnresolvedCount": 0,
  "ExactMatchCount": 351,
  "ReferenceMatchCount": 72,
  "FuzzyConfirmedCount": 5,
  "TimingDifferenceCount": 10,
  "BankErrorCount": 0,
  "AutomationStatus": "Ready"
}

The controller receives the approval, opens the workbook and evidence folder, reviews the timing items and control totals, and approves. Power Automate stores the returned approval identifier, copies the approved workbook, creates the JSON evidence file, and changes the run status to Approved.

If the ledger timing item had no review note, or if the adjusted balance differed by one cent, Power Automate would have blocked the request before the controller received an approval.

Implementation Cost

The following amounts are representative planning assumptions, not vendor quotes or verified client costs. Existing QuickBooks Online and Microsoft 365 subscriptions are treated as existing business systems. Each organization must verify current licensing, connector requirements, tax, and regional pricing.

A business implementing the system entirely with internal staff may not incur the representative professional fee, but it should still account for design, construction, testing, training, documentation, and maintenance time.

Estimated Time and Cost Savings

The calculation uses these representative assumptions:

Current monthly labour hours: Monthly volume × current minutes per record ÷ 60

1,250 × 1.6 ÷ 60 = 33.33 hours

New monthly labour hours: Monthly volume × new minutes per record ÷ 60, plus exception handling and maintenance

(1,250 × 0.30 ÷ 60) + (100 × 4 ÷ 60) + 2 = 6.25 + 6.67 + 2 = 14.92 hours

Monthly hours recovered: Current monthly labour hours minus new monthly labour hours

33.33 - 14.92 = 18.41 hours

Estimated monthly labour value: Monthly hours recovered × loaded hourly labour cost

18.41 × 48 = 883.68

Net estimated monthly value: Monthly labour value minus recurring tool costs

883.68 - 35 = 848.68

Estimated payback period: One-time implementation cost ÷ net estimated monthly value

9,444 ÷ 848.68 = approximately 11.1 months

Recovered time does not automatically reduce payroll. It may provide additional close capacity, quicker investigation, reduced overtime, less dependence on one preparer, and the ability to handle higher transaction volume without adding the same amount of administrative work.

Non-financial benefits include:

• Clear separation between deterministic matches and human decisions
• Fewer email follow-ups
• Consistent amount and reference normalization
• Better visibility into unresolved items
• Repeatable one-to-one matching controls
• Durable approval evidence
• Faster controller review
• Better reporting on recurring exceptions
• Reduced risk of approving an incomplete reconciliation

Readers should replace volume, handling time, exception rate, review time, labour rate, maintenance, software allocation, and implementation cost with their own measured figures.

Adding AI to the Automation

AI is optional and should be added only after the deterministic process works reliably. Most reconciliation controls do not require AI.

Required fields, exact amount comparison, date tolerances, normalized references, one-to-one constraints, approval thresholds, permissions, and balance calculations should remain rule-based. AI is not more reliable than these deterministic controls.

Potential AI uses include:

• Summarizing long bank and ledger descriptions
• Suggesting an exception category
• Identifying likely missing information in reviewer notes
• Drafting a short investigation checklist
• Grouping recurring description patterns for reporting
• Suggesting whether an item resembles a timing difference, duplicate, fee, or unknown transaction

The core automation creates standardized records, deterministic matches, exceptions, workflow status, approvals, and evidence. AI contributes only to reading and categorizing unstructured exception information.

The Recommended AI Enhancement

The recommended enhancement is AI-assisted exception triage. It runs only for unresolved transactions after exact and reference matching.

• Trigger: A reconciliation enters In Review and unresolved exception rows are available.
• AI input: Masked bank description, masked ledger candidate descriptions, signed amount, date gap, reference-presence flags, prior match results, and permitted exception history.
• Expected output: Summary, suggested category, suggested next action, missing-information list, evidence rationale, and confidence.
• Record update: Write the suggestion to a separate SharePoint exception-triage list keyed by ReconID and row ID.
• Human review: A finance reviewer accepts, changes, or ignores the suggestion.
• Low confidence: Confidence below 0.75 is displayed as no recommendation.
• Prohibited data: Full bank account numbers, credentials, card data, personal banking details, unnecessary personal information, and confidential information outside the exception.
• Failure behavior: Leave the exception unclassified and continue with manual review.

The reusable system instruction is:

You assist a finance employee with bank-reconciliation exception triage.

Use only the supplied transaction facts. Do not invent transactions, documents, accounting entries, approvals, or policies.

You may suggest an exception category and next investigation step. You must not approve a match, post an accounting entry, conclude that fraud occurred, or make a final accounting decision.

An exact amount does not prove that two transactions match. Treat missing references, repeated amounts, conflicting dates, and conflicting descriptions as uncertainty.

Return only JSON matching the provided schema. If the evidence is insufficient, use category unknown, select obtain_supporting_evidence as the next action, and explain what is missing.

The user prompt template is:

Reconciliation ID: {{RECON_ID}}
Account key: {{MASKED_ACCOUNT_KEY}}
Bank row ID: {{BANK_ROW_ID_OR_NULL}}
Ledger row ID: {{LEDGER_ROW_ID_OR_NULL}}
Bank date: {{BANK_DATE_OR_NULL}}
Ledger date: {{LEDGER_DATE_OR_NULL}}
Signed amount in cents: {{AMOUNT_CENTS}}
Bank description: {{MASKED_BANK_DESCRIPTION}}
Ledger description: {{MASKED_LEDGER_DESCRIPTION}}
Bank reference present: {{TRUE_OR_FALSE}}
Ledger reference present: {{TRUE_OR_FALSE}}
Date gap in days: {{DATE_GAP_OR_NULL}}
Exact match result: {{RESULT}}
Reference match result: {{RESULT}}
Fuzzy similarity, if calculated: {{SCORE_OR_NULL}}
Current reviewer notes: {{MASKED_NOTES_OR_EMPTY}}

Classify the exception and recommend the next investigation action. Do not approve or reject the transaction.

Use this strict output schema:

{
  "type": "object",
  "additionalProperties": false,
  "properties": {
    "summary": {
      "type": "string",
      "maxLength": 400
    },
    "category": {
      "type": "string",
      "enum": [
        "timing_difference_candidate",
        "missing_ledger_entry_candidate",
        "duplicate_candidate",
        "bank_error_candidate",
        "description_mismatch",
        "amount_mismatch",
        "date_mismatch",
        "missing_information",
        "unknown"
      ]
    },
    "recommended_next_action": {
      "type": "string",
      "enum": [
        "check_bank_support",
        "check_quickbooks_entry",
        "compare_source_references",
        "check_for_duplicate",
        "request_owner_explanation",
        "obtain_supporting_evidence",
        "manual_finance_review"
      ]
    },
    "missing_information": {
      "type": "array",
      "items": {
        "type": "string"
      },
      "maxItems": 6
    },
    "evidence_rationale": {
      "type": "array",
      "items": {
        "type": "string"
      },
      "maxItems": 6
    },
    "confidence": {
      "type": "number",
      "minimum": 0,
      "maximum": 1
    },
    "requires_human_review": {
      "type": "boolean",
      "const": true
    }
  },
  "required": [
    "summary",
    "category",
    "recommended_next_action",
    "missing_information",
    "evidence_rationale",
    "confidence",
    "requires_human_review"
  ]
}

If an approved OpenAI API connection is used, Power Automate can call the Responses API through an authorized HTTP connection. Replace placeholders and select a currently supported model that accepts structured output.

{
  "model": "YOUR_APPROVED_MODEL",
  "instructions": "You assist a finance employee with bank-reconciliation exception triage. Use only supplied facts. Do not approve matches, post entries, allege fraud, or make final accounting decisions. Return only JSON matching the schema.",
  "input": "Reconciliation ID: REC-2026-05-OPERATING\nAccount key: OPERATING\nBank row ID: OPERATING|B|BANK-580274\nLedger row ID: null\nBank date: 2026-05-14\nLedger date: null\nSigned amount in cents: -184260\nBank description: ACH PMT SUPPLIER INV 88421\nLedger description: null\nBank reference present: true\nLedger reference present: false\nDate gap in days: null\nExact match result: no unique match\nReference match result: no unique match\nFuzzy similarity: null\nCurrent reviewer notes: none",
  "text": {
    "format": {
      "type": "json_schema",
      "name": "reconciliation_exception_triage",
      "strict": true,
      "schema": {
        "type": "object",
        "additionalProperties": false,
        "properties": {
          "summary": {"type": "string", "maxLength": 400},
          "category": {
            "type": "string",
            "enum": [
              "timing_difference_candidate",
              "missing_ledger_entry_candidate",
              "duplicate_candidate",
              "bank_error_candidate",
              "description_mismatch",
              "amount_mismatch",
              "date_mismatch",
              "missing_information",
              "unknown"
            ]
          },
          "recommended_next_action": {
            "type": "string",
            "enum": [
              "check_bank_support",
              "check_quickbooks_entry",
              "compare_source_references",
              "check_for_duplicate",
              "request_owner_explanation",
              "obtain_supporting_evidence",
              "manual_finance_review"
            ]
          },
          "missing_information": {
            "type": "array",
            "items": {"type": "string"},
            "maxItems": 6
          },
          "evidence_rationale": {
            "type": "array",
            "items": {"type": "string"},
            "maxItems": 6
          },
          "confidence": {
            "type": "number",
            "minimum": 0,
            "maximum": 1
          },
          "requires_human_review": {
            "type": "boolean",
            "const": true
          }
        },
        "required": [
          "summary",
          "category",
          "recommended_next_action",
          "missing_information",
          "evidence_rationale",
          "confidence",
          "requires_human_review"
        ]
      }
    }
  }
}

The HTTP request uses POST, an authorization bearer header containing YOUR_API_KEY, and a JSON content type. Credentials must be stored in an approved connection or secret-management facility, not directly in visible flow steps.

Power Automate parses the returned JSON, validates every enum and the confidence range, and writes the result to the triage list. A malformed response, HTTP 400 response, or schema failure goes to manual review without changing reconciliation status. HTTP 429 and temporary server failures receive a limited retry with increasing delay.

Log model identifier, request timestamp, row ID, response status, token or usage information when available, and reviewer disposition. Do not log prohibited source data in general-purpose flow messages.

Benefits of the AI Enhancement

• Less time reading repetitive bank descriptions
• More consistent initial exception categories
• Faster identification of missing references or support
• Shorter summaries for controller review
• Improved reporting on recurring exception themes
• Better prioritization of unfamiliar or incomplete transactions

These are AI-specific benefits. Standardized imports, exact matching, reference matching, one-to-one controls, status management, approval routing, and evidence retention already exist without AI.

What Remains Rule-Based or Human-Controlled

Estimating the Additional Value of AI

The representative core automation produces approximately 100 exceptions per month. Without AI, each exception takes an average of four minutes to read, categorize, investigate, and document.

Additional monthly capacity: 6.67 - 5.00 = 1.67 hours

Representative labour value: 1.67 × 48 = 80.16

Less representative AI usage allowance: 80.16 - 10 = 70.16 per month

The estimate assumes all 100 outputs are reviewed, approximately 15 percent require category correction, and approximately 2 percent fail or return no useful recommendation. AI does not eliminate review or guarantee accuracy.

Testing Checklist

Use masked sample data before processing real financial information.

Ongoing Maintenance

The controller is the primary business owner. A finance systems administrator or automation specialist is the technical owner, with a named backup for close periods.

Backups must cover SharePoint content and any tenant-specific recovery requirements. Version history is useful but should not be treated as the only backup strategy without confirming organizational recovery controls.

Thresholds should not be adjusted merely to increase the apparent match rate. Changes require historical testing, documented rationale, and controller approval.

When to Move to Dedicated Software

The Excel and Power Automate design can remain appropriate while volumes, entities, permissions, and exception patterns are manageable. Replacement is not automatic.

Dedicated reconciliation or financial-close software should be considered when:

• Transaction volume makes desktop refresh or workbook review too slow.
• The business operates many bank accounts, entities, currencies, or locations.
• One-to-many and many-to-many matching become common.
• Formal segregation-of-duties controls exceed SharePoint and workbook permissions.
• Regulators or auditors require specialized immutable audit capabilities.
• Direct bank feeds and accounting APIs become mandatory.
• Exception rates remain high despite process correction.
• Workbook and query maintenance consumes excessive technical time.
• Multiple preparers need simultaneous transaction-level review.
• Advanced close orchestration, certification, or intercompany matching is required.
• Customer-facing, mobile, or offline access becomes necessary.
• Vendor support and contractual service levels are required.
• Security risk increases beyond what the file-based architecture can reasonably control.

Before replacing the implementation, the business should determine whether the underlying problem is software capacity, poor source data, inconsistent accounting references, or an unresolved operating process. Dedicated software will not correct weak source discipline by itself.

Implementation Checklist

• Confirm the account scope, statement periods, volumes, owners, and approval policy.
• Document actual bank and QuickBooks export formats.
• Verify the signed-amount convention with known transactions.
• Select Excel, Power Query, SharePoint, and Power Automate roles.
• Confirm licensing and connector requirements.
• Create test and production accounts, connections, groups, and delegates.
• Create the Reconciliation Runs list and indexed operational views.
• Create the SharePoint template, source, support, working, and approved folders.
• Build the canonical bank and ledger import tables.
• Create stable row identifiers and unique ReconID controls.
• Build exact one-to-one matching.
• Build normalized reference matching with a date tolerance.
• Build controlled fuzzy suggestions with threshold, margin, and collision checks.
• Create a separate review-decision table.
• Define timing, bank-error, investigation, and rejection rules.
• Build the run summary and balance-control calculation.
• Load query results into named Excel output tables.
• Create the run-setup Power Automate flow.
• Create readiness validation and approval routing.
• Add sequential secondary approval where policy requires it.
• Add reminders, escalations, unavailable-approver handling, and reassignment controls.
• Add evidence-copy and approval-evidence creation actions.
• Configure Try, Catch, and Finally scopes with run-after behavior.
• Set limited retries for transient errors and manual recovery for validation failures.
• Create blocked, overdue, rejected, failure, and manual-review views.
• Apply least-privilege permissions and shared-link restrictions.
• Test duplicate events, duplicate IDs, invalid inputs, one-cent differences, and failed connections.
• Complete user acceptance testing with preparers and approvers.
• Pilot one account before phased deployment.
• Document implementation cost and replace representative assumptions with actual figures.
• Measure current handling time, exception rate, maintenance, and loaded labour cost.
• Add AI only after the deterministic process is stable.
• Keep fuzzy acceptance, accounting treatment, bank errors, and final approval human-controlled.
• Assign primary and backup maintenance owners.
• Define the volume, security, audit, and complexity criteria that would justify dedicated software.