Automating Budget vs Actual Reporting in Power BI

The Business Situation

Alder Creek Equipment is a fictional 92-person industrial equipment distributor. Its finance department consists of a finance director, a senior accountant, an accounting analyst, and an FP&A analyst. Six department managers contribute budget and forecast information, while the executive team reviews consolidated financial performance.

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 company processes approximately 4,800 general-ledger transaction lines each month. Its chart of accounts contains about 220 active expense accounts, which finance groups into 28 reporting categories. Reporting covers six departments and 18 departmental cost centers.

Actual spending is exported from the accounting system as CSV files. Budgets and forecasts are maintained in Excel. Before this implementation, department files were separate, account mappings were copied between workbooks, and the monthly report was assembled through formulas, lookups, and manual checks.

Finance needed monthly and year-to-date reporting that consistently mapped accounts and departments, highlighted material variances, captured explanations, and incorporated approved forecast revisions. It also needed a controlled review process so that exceptions did not remain buried in email.

The selected implementation connected Excel, Power Query, Power BI, Power Automate, SharePoint Online, and Microsoft Teams. Excel remained the controlled planning input, Power Query standardized the data, Power BI became the reporting layer, and Power Automate created and monitored variance reviews.

The Existing Process

The original reporting process followed the same sequence after each accounting close:

1. The senior accountant exported the general ledger to a CSV file.
2. The FP&A analyst copied the export into a monthly Excel workbook.
3. Lookup formulas assigned reporting account groups and department names.
4. Six department budget files were opened and copied into a consolidated workbook.
5. The analyst checked for missing account mappings and corrected formulas.
6. Pivot tables were refreshed and copied into department-specific report tabs.
7. Variance explanations were requested through email.
8. Managers replied in email or entered notes into separate spreadsheet copies.
9. The analyst manually consolidated explanations and forecast changes.
10. The finance director reviewed the completed workbook before it was distributed.

The main control problem was not the arithmetic. Excel could calculate a variance correctly. The problem was maintaining one reliable mapping, one current reporting model, and one auditable review process across disconnected files and messages.

What the New System Needed to Do

Finance documented the requirements before selecting a reporting platform. This prevented the project from becoming a dashboard redesign that left the underlying data and review process unchanged.

Finance also required deterministic calculations. Account mappings, thresholds, due dates, approval limits, and report totals could not depend on artificial intelligence. Those controls needed explicit rules that could be tested and audited.

Implementation Approaches Considered

Improved Excel

A consolidated Excel workbook would have reduced some copying. It would not have provided dependable workflow ownership, centralized alerting, or controlled distribution. It also would have left finance responsible for maintaining multiple report copies.

Excel and Power Query

Power Query could combine files and enforce mappings without Power BI. This was a credible option for a finance-only report. It became less suitable once department access, executive dashboards, scheduled refresh, and automated variance reviews were included.

Google Sheets and Looker Studio

Sheets and Looker Studio could support a similar design, particularly for an organization already operating in Google Workspace. Alder Creek Equipment already used Microsoft 365, SharePoint, Teams, and Excel, so moving finance inputs into a separate environment would have added identity, training, and governance work.

Accounting-System Reporting

The accounting system could report actual transactions and standard budget comparisons. It did not provide the desired cross-functional workflow, custom materiality logic, structured explanation register, or flexible forecast adjustment process. The accounting platform remained the source of actuals rather than becoming the complete reporting solution.

Selected Approach

Power BI, Power Query, Excel, and Power Automate provided the required controls without introducing a full enterprise planning platform. The approach was appropriate for the transaction volume and the finance team’s existing Microsoft skills.

The Selected Solution

The intake design used restricted SharePoint folders rather than email attachments. Finance uploaded accounting exports to a defined folder, while the controlled Excel planning workbook remained in a separate finance-only folder.

The authoritative sources were deliberately separated:

• The accounting system remained authoritative for actual spending.
• The approved Excel budget remained authoritative for the budget baseline.
• The Excel forecast table remained authoritative for the starting forecast.
• The SharePoint Forecast Adjustments list became authoritative for approved revisions.
• The SharePoint Variance Reviews list became the workflow system of record.
• The Power BI semantic model became the reporting and calculation layer.

Manual consolidation, lookup maintenance across multiple workbooks, report-tab copying, and email chasing were removed. Finance retained control over mappings, threshold changes, forecast approval, period closure, and any decision to exclude an exceptional item.

System Architecture and Data Flow

• Intake: Accounting CSV exports and a controlled Excel planning workbook stored in SharePoint Online.
• System of record: Accounting system for actuals, Excel for planning baselines, and SharePoint lists for review workflow and approved adjustments.
• Automation layer: Power Query for data preparation and Power Automate for workflow orchestration.
• Document storage: SharePoint document libraries with versioning and restricted folders.
• Notifications: Microsoft Teams and email through Power Automate.
• Reporting: A Power BI semantic model and role-specific reports.
• AI layer: Optional summary drafting after the deterministic reporting and review process is stable.

1. Accounting export: The senior accountant places a CSV file in the Actuals Incoming folder. A Power Automate file-intake flow checks the extension, filename pattern, file size, and duplicate filename. Invalid files move to Quarantine.
2. Planning input: Finance updates Excel tables for budget, baseline forecast, account mappings, department mappings, and reporting thresholds. Excel data validation restricts codes and dates.
3. Scheduled refresh: Power BI Service refreshes the semantic model. Power Query retrieves the SharePoint files and Excel tables using the refresh owner’s Microsoft 365 connection.
4. Validation and transformation: Power Query checks required columns, converts data types, removes duplicate transaction IDs, joins account and department maps, and calculates positive reporting amounts.
5. Model update: Valid rows load into actual, budget, forecast, and dimension tables. Approved SharePoint forecast adjustments load as a separate fact table.
6. Variance calculation: DAX measures calculate monthly actuals, budgets, forecasts, year-to-date totals, amount variances, percentage variances, and exception flags.
7. Freshness check: A scheduled Power Automate flow queries the semantic model after the planned refresh window. If the model timestamp is stale, it stops exception processing and alerts the technical owner.
8. Exception query: Power Automate runs a DAX query against the semantic model. The query returns only department and account-group combinations that exceed configured materiality thresholds.
9. Review creation: The flow searches SharePoint for the deterministic exception key. It creates a new review when no record exists or updates the existing record with current values.
10. Owner alert: New and reopened records generate Teams and email notifications containing the variance, due date, and SharePoint review link.
11. Manager review: The assigned manager adds an explanation, supporting evidence, and any proposed forecast adjustment.
12. Approval: Power Automate routes forecast changes to the finance director. Changes above the executive threshold receive a second approval.
13. Reporting update: Approved adjustments are included during the next semantic-model refresh. Power BI shows the revised forecast and the recorded explanation.
14. Failure path: Refresh, query, list, file, or notification failures are written to an Automation Failures list and sent to the support owner. The affected review is marked for manual attention when possible.

Data Structure

The model uses a star-style structure. Dimension tables describe dates, reporting departments, account groups, and source accounts. Fact tables contain transactions, monthly plans, approved adjustments, and review activity.

Variance Review Fields

Forecast Adjustment Fields

A separate Forecast Adjustments list supports one or more revised months for a single variance review. Each adjustment contains an AdjustmentKey, parent ExceptionKey, ForecastMonth, DepartmentCode, AccountGroupCode, BaseForecastAmount, ProposedForecastAmount, AdjustmentAmount, ApprovalStatus, Approver, ApprovalDate, ApprovalComments, and SourceHash.

Variance Reviews have a one-to-many relationship with Forecast Adjustments through ExceptionKey. Power BI relates approved adjustments to DimDate, DimDepartment, and DimAccountGroup. The reporting measure adds approved AdjustmentAmount values to the baseline forecast.

SharePoint indexes are created for ExceptionKey, Status, Owner, DueDate, MonthStart, and ApprovalStatus. Unique-value enforcement is enabled for ExceptionKey and AdjustmentKey.

Workflow Statuses and Ownership

A review can move backward when information is incomplete, an approver returns a forecast change, or finance reassigns ownership. Rejection does not delete the review. It records that the proposed forecast update was not accepted.

Closure remains human-controlled. Power Automate can verify that required fields are present, but finance decides whether an explanation is sufficient and whether the reporting period is ready to close.

Step-by-Step Implementation

Step 1: Prepare the Accounts and Permissions

1. Create separate SharePoint locations for development and production. Use a test site or clearly named test folders and lists so sample records cannot enter the production report.
2. Create a restricted Finance Reporting document library with Planning, Actuals Incoming, Actuals Processed, Quarantine, Variance Evidence, and Period Archive folders.
3. Enable version history on the planning and evidence locations. Restrict deletion and sharing to finance owners.
4. Create the Variance Reviews, Forecast Adjustments, Automation Runs, Automation Failures, and Approver Delegation lists.
5. Create a Power BI development workspace and production workspace. Limit workspace administration to the finance systems owner and backup owner.
6. Confirm that report creators have Power BI Desktop and that report consumers have the required sharing or capacity rights. Licensing terms should be checked against the current Microsoft product terms.
7. Confirm that Power Automate can use SharePoint, Power BI, Approvals, Teams, and email connections in the organization’s environment. The action may refer to the Power BI object as a dataset even when the service uses the term semantic model.
8. Create or designate a licensed automation identity according to company policy. It should not be a former employee’s personal connection and should have multifactor authentication and documented recovery ownership.
9. Grant the refresh identity read access to source files and lists. Grant the workflow identity permission to create and update review records, but not permission to change accounting exports or budget baselines.
10. Grant the workflow identity Build access to the Power BI semantic model if required by the query action and tenant configuration.
11. Create test users representing a finance administrator, department manager, executive approver, delegated approver, and unauthorized user.
12. Document all connections in an integration register, including owner, purpose, environment, authentication method, and review date.

Because every production source is cloud-based in SharePoint Online, the selected design does not require an on-premises data gateway. A gateway would be required if the accounting source later moved to an on-premises file share or database.

Passwords and API keys are not placed in Excel, Power Query, report parameters visible to users, or flow descriptions. The optional AI credential is stored through an approved secret-management method and the relevant Power Automate inputs and outputs are marked secure.

Step 2: Build the Intake

The implementation has three intake paths: actual transaction files, finance-controlled planning data, and manager-entered review information.

Accounting Export Intake

Files use the naming convention GL_Actuals_YYYY-MM_Batch.csv. The intake flow is triggered when a file is created in Actuals Incoming. It verifies that the file is a non-empty CSV and that another processed file does not already have the same name and content hash.

A valid file is recorded in Automation Runs and left available for the next Power BI refresh. An invalid or duplicate file moves to Quarantine and generates a finance notification. Power Query performs the definitive schema, data-type, and mapping checks during refresh.

Planning Intake

The finance-only workbook is named FinancePlanning.xlsx. It contains formatted Excel tables named tblBudget, tblForecast, tblAccountMap, and tblDepartmentMap. Formatted Excel tables are required because Power Query can address them by stable object name.

Excel data validation restricts department and account-group codes. MonthStart must be the first day of a month. Amount fields accept numeric values. Conditional formatting highlights blank codes, duplicate composite keys, negative values where they are not expected, and dates outside the planning horizon.

Review Intake

Department managers use the SharePoint Variance Reviews item form. Required fields are Explanation and ExceptionType. A forecast update is optional. If a manager creates a related Forecast Adjustment, ForecastMonth and ProposedForecastAmount become required.

Supporting evidence is required by policy for high-value adjustments. Evidence is stored in the restricted Variance Evidence library rather than in email. The item form includes a privacy notice telling users not to enter payroll details, bank information, medical information, or other unnecessary personal data.

The intake is restricted to authenticated employees, so public spam controls are unnecessary. Incomplete submissions remain in In Review or Returned status and cannot enter approval.

Step 3: Create the System of Record

Create the SharePoint lists with the fields defined in the Data Structure section. Set default values as follows:

• Status: New
• Priority: Normal
• ApprovalStatus: Not Required
• AutomationStatus: Complete
• RetryCount: 0
• DueDate: blank until assigned by automation

Enable unique values for ExceptionKey and AdjustmentKey. Index the frequently filtered fields before the list grows. Enable version history so changes to explanations, ownership, amounts, and statuses remain visible.

The readable review identifier is generated as VAR-YYYYMM-DEPARTMENT-ACCOUNTGROUP. The underlying ExceptionKey uses separators and stable codes, for example 202605|D20|FREIGHT.

The same key is reused every time the exception scanner finds that month, department, and account group. This prevents daily scans from creating duplicate reviews.

Create the following list views:

• My Open Reviews
• New Reviews
• Awaiting Finance Approval
• Overdue Reviews
• Returned for Information
• Automation Errors
• Recently Closed
• Forecast Adjustments Pending Approval

In Power BI Desktop, build relationships from DimDate to each fact date, from DimDepartment to each department code, and from DimAccountGroup to budgets, forecasts, adjustments, and account mappings. DimAccountGroup filters DimAccount, which then filters FactActual.

Relationships should normally be single-direction from dimensions to facts. Avoid many-to-many relationships for account mappings. Every source account must map to exactly one active reporting account group for a given reporting version.

Step 4: Connect the Tools

Power Query receives file content, table content, filenames, modified times, and list records. It converts codes to text so that leading zeros are preserved. Amounts become decimal numbers, dates become date values, and timestamps remain date-time values.

Power Automate stores the SharePoint item ID after record creation. Approval identifiers and response details are stored on the Forecast Adjustment and summarized on the parent review. A failed notification does not reverse a successfully created review.

Step 5: Build the Core Automation

Automation 1: Validate File Intake

• Trigger: A file is created in the Actuals Incoming folder.
• Conditions: Extension is CSV, filename matches policy, size is greater than zero, and no processed file has the same name and hash.
• Actions: Record the intake, mark it Ready for Refresh, and notify finance of acceptance.
• Fields updated: Filename, file hash, received time, uploader, status, and run identifier.
• Notification: Confirmation for valid files or quarantine notice for invalid files.
• Exception: Move invalid files to Quarantine and create an Automation Failure item.

Automation 2: Refresh and Detect Variances

• Trigger: Weekday recurrence after the scheduled Power BI refresh window.
• Conditions: Model refresh timestamp is within three hours and the reporting period is open.
• Actions: Query material exceptions, find each ExceptionKey in SharePoint, and create or update the review.
• Fields updated: Current amounts, variance, owner, priority, last detected date, automation status, and run timestamp.
• Notification: Send a Teams and email message only for newly created or intentionally reopened records.
• Exception: Stop processing stale data and notify the finance systems owner.

The exact action order is:

1. Create an Automation Run record with status Started.
2. Query the semantic model for RefreshUtc.
3. Compare RefreshUtc with the accepted freshness window.
4. If stale, mark the run Failed, notify support, and terminate.
5. Run the DAX exception query.
6. Parse the returned rows against the expected JSON schema.
7. Process rows sequentially to reduce unique-key race conditions.
8. Search Variance Reviews using ExceptionKey.
9. Create a new review when the search returns zero items.
10. Update the existing review when one item is returned.
11. Route multiple matches to Automation Error because the uniqueness control has failed.
12. For new or reopened reviews, create an evidence folder and send notifications.
13. Mark the Automation Run Completed with row counts and duration.

Closed records are not reopened merely because the same static numbers remain above threshold. The flow compares a stored SourceHash derived from month, department, account group, actual, and budget. A closed review is reopened only when the hash changes materially and the period remains open.

The flow is divided into Try, Catch, and Finally scopes. Catch runs after failure, timeout, or skipped actions in Try. Finally updates the run log regardless of the preceding result. Connector actions use exponential retry for transient failures, while validation failures do not retry.

Step 6: Add Approvals, Reminders, and Escalations

Forecast Approval Flow

• Trigger: A Forecast Adjustment item is created or modified with SubmitForApproval set to Yes.
• Conditions: Required fields are complete, the adjustment is not already pending or approved, and the source hash has not already been processed.
• Actions: Calculate AdjustmentAmount, set Pending, start finance approval, and conditionally start executive approval.
• Fields updated: ApprovalStatus, approver, response date, comments, external response identifier, and adjustment amount.
• Notification: Submitter receives approval, rejection, or return outcome.
• Exception: Missing approver or failed approval action creates a finance support item.

The first approval always goes to the finance director or an active delegate. If the absolute AdjustmentAmount is at least $25,000, a second sequential approval goes to the designated executive approver. The threshold is a documented business policy, not an AI recommendation.

If the finance director is unavailable, the flow checks the Approver Delegation list for an active delegate whose start and end dates include the current date. If none exists, the item moves to Automation Error and finance receives an escalation.

Rejection sets ApprovalStatus to Rejected and leaves the proposed amount visible for audit purposes. A return sets the parent review to Returned and requires new information before resubmission. Resubmission creates a new approval response record rather than overwriting the previous evidence.

Reminder and Escalation Flow

A separate daily flow retrieves reviews in New, In Review, Returned, or an approval status. It uses indexed filters for Status and DueDate.

• At 48 hours without action, the owner receives a reminder.
• At 72 hours, the owner and finance director receive an escalation.
• High-priority reviews receive their first reminder after 24 hours.
• Overdue reviews remain on the daily escalation report until action is recorded.
• A notification timestamp prevents more than one routine reminder per day.

Reminder delivery is not treated as approval evidence. Approval outcomes, responders, comments, and timestamps are stored separately on the adjustment item.

Step 7: Add Documents and File Management

The document library uses this structure:

Finance Reporting/
  Planning/
    FinancePlanning.xlsx
  Actuals Incoming/
  Actuals Processed/
    2026/
      2026-05/
  Quarantine/
  Variance Evidence/
    2026/
      VAR-202605-D20-FREIGHT/
  Period Archive/
    2026/
      2026-05/

Accounting files use immutable names that include period and batch. A corrected export receives a new batch suffix rather than silently replacing the original. SharePoint versioning remains enabled as an additional control.

When a review is created, Power Automate creates the year folder if necessary and then creates an evidence folder named after the readable review identifier. The folder URL is written to DocumentLink.

Department managers can add supporting files to the evidence folder but cannot edit budget baselines or accounting exports. External sharing is disabled. Filename conventions exclude personal names and sensitive identifiers.

If folder creation fails, the review is still created. DocumentLink remains blank, AutomationStatus becomes Warning, and the support owner receives a repair task. A recovery flow can create the folder later and update the link.

Closed-period source files, mapping versions, and an exported copy of the approved planning tables are copied into the Period Archive. Retention follows the company’s financial record policy. File-size and library limits are reviewed before adding transaction-level supporting documents.

Step 8: Add Reporting and Operational Views

The Power BI report contains the following pages:

• Executive Summary: Actual, budget, forecast, amount variance, percentage variance, and year-to-date performance.
• Department Detail: Monthly results by account group with drill-through to permitted detail.
• Variance Waterfall: Contribution of account groups to the total department variance.
• Forecast Outlook: Baseline forecast, approved adjustments, current forecast, and projected year-end result.
• Review Queue: New, overdue, returned, approved, and closed variance reviews.
• Data Quality: Refresh timestamp, source row counts, mapping version, and validation status.
• Automation Health: Failed runs, retries, stale records, and notification warnings.

Operational SharePoint views complement the report. SharePoint is used for immediate workflow action, while Power BI is used for analysis after refresh.

Useful report filters include fiscal year, month, department, account group, review status, exception type, owner, and priority. Calculated fields include days open, days overdue, current forecast, year-to-date variance, and percentage of total variance.

The report owner checks source row counts against the accounting export. A significant unexpected drop in row volume or a stale RefreshUtc value creates a visible warning. Alert thresholds are maintained in the Excel department map so finance can change them through a controlled process.

Step 9: Add Security and Governance Controls

• Use least-privilege access for SharePoint, Power BI, Power Automate, and approval connections.
• Restrict mapping and planning workbook changes to finance.
• Apply row-level security so department managers see only approved departmental data.
• Provide finance and executive users with explicitly assigned broader access.
• Exclude sensitive payroll descriptions, bank details, and unnecessary vendor personal information from the reporting model.
• Do not assume row-level security hides sensitive columns. Publish a separate summarized report for broad audiences if transaction descriptions should remain finance-only.
• Disable anonymous and external links for financial source and evidence libraries.
• Enable SharePoint version history and preserve Power Automate run history according to policy.
• Store optional API secrets through approved secret management and enable secure action inputs and outputs.
• Review former employee access promptly and transfer flow ownership before disabling accounts.
• Document manual overrides, period reopening, mapping changes, and threshold changes.
• Require human approval for forecast changes and period closure.
• Prohibit the optional AI service from receiving payroll data, bank details, tax identifiers, or unrestricted transaction descriptions.

A dynamic row-level security mapping table contains UserEmail and ReportingDepartmentCode. In Power BI, a restricted role can use the following expression:

LOWER(DimUserDepartment[UserEmail]) = LOWER(USERPRINCIPALNAME())

The relationship from DimUserDepartment to DimDepartment must be tested carefully so that security filters reach every fact table. Finance-wide access should use a separate role or report audience governed by documented membership.

Step 10: Deploy and Test

1. Build the model in a development Power BI file using sample accounting and planning data.
2. Create development SharePoint lists and folders with the same field structure as production.
3. Build flows against development resources and name them clearly with a development prefix.
4. Test transformations with normal, missing, duplicate, negative, unmapped, and malformed records.
5. Validate Power BI totals against a manually checked control workbook for at least two periods.
6. Run user acceptance testing with finance, one department manager, one executive approver, and the backup support owner.
7. Pilot the workflow with two departments before enabling all six.
8. Freeze production mapping changes during deployment.
9. Publish the production semantic model and report, then configure credentials and refresh schedules.
10. Copy or recreate flows using production connection references and environment variables where available.
11. Activate the file-intake flow first, then refresh monitoring, exception detection, reminders, and approvals.
12. Monitor every run during the first two close cycles.
13. Keep the previous reporting workbook available as a read-only rollback reference until finance signs off.
14. Document source locations, fields, mappings, thresholds, flow ownership, recovery steps, and support contacts.
15. Communicate the launch date, manager responsibilities, due dates, and support process.

The rollback plan disables production flows, stops scheduled refresh, restores the previous report distribution method, and preserves all records created before rollback. It does not delete failed or partial workflow records.

Code and Configuration

The solution uses Power Query M, DAX, and Power Automate expressions. The code below is intended for Power BI Desktop. Replace the tenant URL, site path, folder paths, file names, and email placeholders before use.

Reusable Power Query Function for Excel Tables

Create a blank query in Power BI Desktop, open the Advanced Editor, paste this code, and name the query fnExcelTable. The refresh identity needs read access to the SharePoint site and workbook.

let
    SiteUrl = "https://YOUR_TENANT.sharepoint.com/sites/Finance",
    WorkbookFolder =
        SiteUrl & "/Shared Documents/Finance Reporting/Planning/",

    fnExcelTable =
        (WorkbookName as text, ExcelTableName as text) as table =>
        let
            Files = SharePoint.Files(
                SiteUrl,
                [ApiVersion = 15]
            ),

            Matches = Table.SelectRows(
                Files,
                each
                    [Name] = WorkbookName
                    and [Folder Path] = WorkbookFolder
                    and not Record.FieldOrDefault(
                        [Attributes],
                        "Hidden",
                        false
                    )
            ),

            FileContent =
                if Table.RowCount(Matches) = 1 then
                    Matches{0}[Content]
                else
                    error Error.Record(
                        "Workbook lookup failed",
                        "Expected exactly one matching workbook.",
                        [
                            Workbook = WorkbookName,
                            MatchCount = Table.RowCount(Matches)
                        ]
                    ),

            WorkbookObjects = Excel.Workbook(
                FileContent,
                null,
                true
            ),

            TableMatches = Table.SelectRows(
                WorkbookObjects,
                each
                    [Item] = ExcelTableName
                    and [Kind] = "Table"
            ),

            Result =
                if Table.RowCount(TableMatches) = 1 then
                    TableMatches{0}[Data]
                else
                    error Error.Record(
                        "Excel table lookup failed",
                        "Expected exactly one matching Excel table.",
                        [
                            Workbook = WorkbookName,
                            ExcelTable = ExcelTableName,
                            MatchCount = Table.RowCount(TableMatches)
                        ]
                    )
        in
            Result
in
    fnExcelTable

Test the function by creating a blank query with fnExcelTable("FinancePlanning.xlsx", "tblBudget"). A lookup error usually means the SharePoint folder path, workbook name, or formatted table name does not match exactly.

Account Mapping Query

Create another blank query named DimAccountMap.

let
    Raw = fnExcelTable(
        "FinancePlanning.xlsx",
        "tblAccountMap"
    ),

    Selected = Table.SelectColumns(
        Raw,
        {
            "AccountCode",
            "AccountName",
            "AccountGroupCode",
            "AccountGroupName",
            "Category",
            "SignMultiplier",
            "IncludeInReport"
        }
    ),

    Typed = Table.TransformColumnTypes(
        Selected,
        {
            {"AccountCode", type text},
            {"AccountName", type text},
            {"AccountGroupCode", type text},
            {"AccountGroupName", type text},
            {"Category", type text},
            {"SignMultiplier", Int64.Type},
            {"IncludeInReport", type logical}
        }
    ),

    Cleaned = Table.TransformColumns(
        Typed,
        {
            {"AccountCode", Text.Trim, type text},
            {"AccountGroupCode", Text.Trim, type text},
            {"AccountName", Text.Trim, type text},
            {"AccountGroupName", Text.Trim, type text}
        }
    ),

    InvalidRows = Table.SelectRows(
        Cleaned,
        each
            [AccountCode] = null
            or [AccountCode] = ""
            or [AccountGroupCode] = null
            or [AccountGroupCode] = ""
            or not List.Contains({-1, 1}, [SignMultiplier])
    ),

    DuplicateKeys = Table.SelectRows(
        Table.Group(
            Cleaned,
            {"AccountCode"},
            {{"RowCount", each Table.RowCount(_), Int64.Type}}
        ),
        each [RowCount] > 1
    ),

    Output =
        if Table.RowCount(InvalidRows) > 0 then
            error Error.Record(
                "Invalid account mapping",
                "Blank keys or invalid sign multipliers were found.",
                [InvalidRowCount = Table.RowCount(InvalidRows)]
            )
        else if Table.RowCount(DuplicateKeys) > 0 then
            error Error.Record(
                "Duplicate account mapping",
                "Each source account must have one mapping.",
                [DuplicateKeyCount = Table.RowCount(DuplicateKeys)]
            )
        else
            Cleaned
in
    Output

Department Mapping Query

Create a query named DimDepartmentMap.

let
    Raw = fnExcelTable(
        "FinancePlanning.xlsx",
        "tblDepartmentMap"
    ),

    Selected = Table.SelectColumns(
        Raw,
        {
            "SourceDepartmentCode",
            "ReportingDepartmentCode",
            "DepartmentName",
            "OwnerEmail",
            "VarianceAmountThreshold",
            "VariancePctThreshold",
            "MinimumBudgetForPct"
        }
    ),

    Typed = Table.TransformColumnTypes(
        Selected,
        {
            {"SourceDepartmentCode", type text},
            {"ReportingDepartmentCode", type text},
            {"DepartmentName", type text},
            {"OwnerEmail", type text},
            {"VarianceAmountThreshold", type number},
            {"VariancePctThreshold", type number},
            {"MinimumBudgetForPct", type number}
        }
    ),

    Cleaned = Table.TransformColumns(
        Typed,
        {
            {"SourceDepartmentCode", Text.Trim, type text},
            {"ReportingDepartmentCode", Text.Trim, type text},
            {"DepartmentName", Text.Trim, type text},
            {"OwnerEmail", each Text.Lower(Text.Trim(_)), type text}
        }
    ),

    InvalidRows = Table.SelectRows(
        Cleaned,
        each
            [SourceDepartmentCode] = null
            or [SourceDepartmentCode] = ""
            or [ReportingDepartmentCode] = null
            or [ReportingDepartmentCode] = ""
            or [OwnerEmail] = null
            or not Text.Contains([OwnerEmail], "@")
            or [VarianceAmountThreshold] < 0
            or [VariancePctThreshold] < 0
            or [MinimumBudgetForPct] < 0
    ),

    DuplicateKeys = Table.SelectRows(
        Table.Group(
            Cleaned,
            {"SourceDepartmentCode"},
            {{"RowCount", each Table.RowCount(_), Int64.Type}}
        ),
        each [RowCount] > 1
    ),

    Output =
        if Table.RowCount(InvalidRows) > 0 then
            error Error.Record(
                "Invalid department mapping",
                "Required department values are missing or invalid.",
                [InvalidRowCount = Table.RowCount(InvalidRows)]
            )
        else if Table.RowCount(DuplicateKeys) > 0 then
            error Error.Record(
                "Duplicate department mapping",
                "Each source department must have one mapping.",
                [DuplicateKeyCount = Table.RowCount(DuplicateKeys)]
            )
        else
            Cleaned
in
    Output

Actual Transaction Query

Create a query named FactActual. This query combines every CSV file in the configured actuals folder, verifies required values, removes duplicate EntryID values, applies mappings, and calculates the reporting amount.

let
    SiteUrl =
        "https://YOUR_TENANT.sharepoint.com/sites/Finance",

    ActualFolder =
        SiteUrl
        & "/Shared Documents/Finance Reporting/Actuals Processed/",

    SourceFiles = SharePoint.Files(
        SiteUrl,
        [ApiVersion = 15]
    ),

    CsvFiles = Table.SelectRows(
        SourceFiles,
        each
            Text.StartsWith([Folder Path], ActualFolder)
            and Text.Lower([Extension]) = ".csv"
            and not Record.FieldOrDefault(
                [Attributes],
                "Hidden",
                false
            )
    ),

    SelectedFileColumns = Table.SelectColumns(
        CsvFiles,
        {
            "Content",
            "Name",
            "Folder Path",
            "Date modified"
        }
    ),

    RenamedFileColumns = Table.RenameColumns(
        SelectedFileColumns,
        {
            {"Name", "SourceFileName"},
            {"Date modified", "SourceModifiedUtc"}
        }
    ),

    Parsed = Table.AddColumn(
        RenamedFileColumns,
        "Rows",
        each
            Table.PromoteHeaders(
                Csv.Document(
                    [Content],
                    [
                        Delimiter = ",",
                        Encoding = 65001,
                        QuoteStyle = QuoteStyle.Csv
                    ]
                ),
                [PromoteAllScalars = true]
            ),
        type table
    ),

    Expanded = Table.ExpandTableColumn(
        Parsed,
        "Rows",
        {
            "EntryID",
            "TransactionDate",
            "AccountCode",
            "DepartmentCode",
            "Amount",
            "DocumentNumber",
            "Description",
            "SourceBatchID"
        },
        {
            "EntryID",
            "TransactionDate",
            "AccountCode",
            "SourceDepartmentCode",
            "Amount",
            "DocumentNumber",
            "Description",
            "SourceBatchID"
        }
    ),

    RemovedContent = Table.RemoveColumns(
        Expanded,
        {"Content", "Folder Path"}
    ),

    Typed = Table.TransformColumnTypes(
        RemovedContent,
        {
            {"EntryID", type text},
            {"TransactionDate", type date},
            {"AccountCode", type text},
            {"SourceDepartmentCode", type text},
            {"Amount", type number},
            {"DocumentNumber", type text},
            {"Description", type text},
            {"SourceBatchID", type text},
            {"SourceFileName", type text},
            {"SourceModifiedUtc", type datetime}
        }
    ),

    RequiredValueErrors = Table.SelectRows(
        Typed,
        each
            [EntryID] = null
            or Text.Trim([EntryID]) = ""
            or [TransactionDate] = null
            or [AccountCode] = null
            or Text.Trim([AccountCode]) = ""
            or [SourceDepartmentCode] = null
            or Text.Trim([SourceDepartmentCode]) = ""
            or [Amount] = null
            or [SourceBatchID] = null
            or Text.Trim([SourceBatchID]) = ""
    ),

    ValidatedRequiredValues =
        if Table.RowCount(RequiredValueErrors) > 0 then
            error Error.Record(
                "Missing required actual values",
                "One or more accounting rows are incomplete.",
                [
                    InvalidRowCount =
                        Table.RowCount(RequiredValueErrors)
                ]
            )
        else
            Typed,

    Sorted = Table.Sort(
        ValidatedRequiredValues,
        {
            {"SourceModifiedUtc", Order.Descending},
            {"SourceFileName", Order.Descending}
        }
    ),

    Buffered = Table.Buffer(Sorted),

    Deduplicated = Table.Distinct(
        Buffered,
        {"EntryID"}
    ),

    JoinedAccounts = Table.NestedJoin(
        Deduplicated,
        {"AccountCode"},
        DimAccountMap,
        {"AccountCode"},
        "AccountMap",
        JoinKind.LeftOuter
    ),

    ExpandedAccounts = Table.ExpandTableColumn(
        JoinedAccounts,
        "AccountMap",
        {
            "AccountName",
            "AccountGroupCode",
            "AccountGroupName",
            "Category",
            "SignMultiplier",
            "IncludeInReport"
        },
        {
            "AccountName",
            "AccountGroupCode",
            "AccountGroupName",
            "Category",
            "SignMultiplier",
            "IncludeInReport"
        }
    ),

    JoinedDepartments = Table.NestedJoin(
        ExpandedAccounts,
        {"SourceDepartmentCode"},
        DimDepartmentMap,
        {"SourceDepartmentCode"},
        "DepartmentMap",
        JoinKind.LeftOuter
    ),

    ExpandedDepartments = Table.ExpandTableColumn(
        JoinedDepartments,
        "DepartmentMap",
        {
            "ReportingDepartmentCode",
            "DepartmentName",
            "OwnerEmail"
        },
        {
            "ReportingDepartmentCode",
            "DepartmentName",
            "OwnerEmail"
        }
    ),

    MappingErrors = Table.SelectRows(
        ExpandedDepartments,
        each
            [AccountGroupCode] = null
            or [SignMultiplier] = null
            or [ReportingDepartmentCode] = null
    ),

    ValidatedMappings =
        if Table.RowCount(MappingErrors) > 0 then
            error Error.Record(
                "Unmapped accounting values",
                "Account or department mappings are incomplete.",
                [
                    UnmappedRowCount =
                        Table.RowCount(MappingErrors)
                ]
            )
        else
            ExpandedDepartments,

    IncludedRows = Table.SelectRows(
        ValidatedMappings,
        each [IncludeInReport] = true
    ),

    AddedMonthStart = Table.AddColumn(
        IncludedRows,
        "MonthStart",
        each Date.StartOfMonth([TransactionDate]),
        type date
    ),

    AddedReportAmount = Table.AddColumn(
        AddedMonthStart,
        "ReportAmount",
        each [Amount] * [SignMultiplier],
        type number
    ),

    Output = Table.SelectColumns(
        AddedReportAmount,
        {
            "EntryID",
            "TransactionDate",
            "MonthStart",
            "AccountCode",
            "AccountName",
            "AccountGroupCode",
            "AccountGroupName",
            "Category",
            "SourceDepartmentCode",
            "ReportingDepartmentCode",
            "DepartmentName",
            "OwnerEmail",
            "Amount",
            "ReportAmount",
            "DocumentNumber",
            "Description",
            "SourceBatchID",
            "SourceFileName",
            "SourceModifiedUtc"
        }
    )
in
    Output

A malformed CSV, missing required column, invalid data type, or incomplete mapping causes refresh to fail instead of silently omitting financial data. Power BI retains the previously successful model until finance corrects the source.

Budget Query

Create a query named FactBudget.

let
    Raw = fnExcelTable(
        "FinancePlanning.xlsx",
        "tblBudget"
    ),

    Selected = Table.SelectColumns(
        Raw,
        {
            "BudgetVersion",
            "FiscalYear",
            "MonthStart",
            "ReportingDepartmentCode",
            "AccountGroupCode",
            "BudgetAmount",
            "UpdatedUtc"
        }
    ),

    Typed = Table.TransformColumnTypes(
        Selected,
        {
            {"BudgetVersion", type text},
            {"FiscalYear", Int64.Type},
            {"MonthStart", type date},
            {"ReportingDepartmentCode", type text},
            {"AccountGroupCode", type text},
            {"BudgetAmount", type number},
            {"UpdatedUtc", type datetime}
        }
    ),

    AccountGroups = List.Buffer(
        List.Distinct(
            DimAccountMap[AccountGroupCode]
        )
    ),

    Departments = List.Buffer(
        List.Distinct(
            DimDepartmentMap[ReportingDepartmentCode]
        )
    ),

    InvalidRows = Table.SelectRows(
        Typed,
        each
            [BudgetVersion] = null
            or [MonthStart] = null
            or Date.StartOfMonth([MonthStart]) <> [MonthStart]
            or not List.Contains(
                Departments,
                [ReportingDepartmentCode]
            )
            or not List.Contains(
                AccountGroups,
                [AccountGroupCode]
            )
            or [BudgetAmount] = null
    ),

    DuplicateKeys = Table.SelectRows(
        Table.Group(
            Typed,
            {
                "BudgetVersion",
                "MonthStart",
                "ReportingDepartmentCode",
                "AccountGroupCode"
            },
            {{"RowCount", each Table.RowCount(_), Int64.Type}}
        ),
        each [RowCount] > 1
    ),

    Output =
        if Table.RowCount(InvalidRows) > 0 then
            error Error.Record(
                "Invalid budget data",
                "Budget rows contain invalid dates, codes, or amounts.",
                [InvalidRowCount = Table.RowCount(InvalidRows)]
            )
        else if Table.RowCount(DuplicateKeys) > 0 then
            error Error.Record(
                "Duplicate budget key",
                "Budget composite keys must be unique.",
                [DuplicateKeyCount = Table.RowCount(DuplicateKeys)]
            )
        else
            Typed
in
    Output

Forecast Query

Create a query named FactForecast.

let
    Raw = fnExcelTable(
        "FinancePlanning.xlsx",
        "tblForecast"
    ),

    Selected = Table.SelectColumns(
        Raw,
        {
            "ForecastVersion",
            "MonthStart",
            "ReportingDepartmentCode",
            "AccountGroupCode",
            "ForecastAmount",
            "UpdatedUtc"
        }
    ),

    Typed = Table.TransformColumnTypes(
        Selected,
        {
            {"ForecastVersion", type text},
            {"MonthStart", type date},
            {"ReportingDepartmentCode", type text},
            {"AccountGroupCode", type text},
            {"ForecastAmount", type number},
            {"UpdatedUtc", type datetime}
        }
    ),

    AccountGroups = List.Buffer(
        List.Distinct(
            DimAccountMap[AccountGroupCode]
        )
    ),

    Departments = List.Buffer(
        List.Distinct(
            DimDepartmentMap[ReportingDepartmentCode]
        )
    ),

    InvalidRows = Table.SelectRows(
        Typed,
        each
            [ForecastVersion] = null
            or [MonthStart] = null
            or Date.StartOfMonth([MonthStart]) <> [MonthStart]
            or not List.Contains(
                Departments,
                [ReportingDepartmentCode]
            )
            or not List.Contains(
                AccountGroups,
                [AccountGroupCode]
            )
            or [ForecastAmount] = null
    ),

    DuplicateKeys = Table.SelectRows(
        Table.Group(
            Typed,
            {
                "ForecastVersion",
                "MonthStart",
                "ReportingDepartmentCode",
                "AccountGroupCode"
            },
            {{"RowCount", each Table.RowCount(_), Int64.Type}}
        ),
        each [RowCount] > 1
    ),

    Output =
        if Table.RowCount(InvalidRows) > 0 then
            error Error.Record(
                "Invalid forecast data",
                "Forecast rows contain invalid dates, codes, or amounts.",
                [InvalidRowCount = Table.RowCount(InvalidRows)]
            )
        else if Table.RowCount(DuplicateKeys) > 0 then
            error Error.Record(
                "Duplicate forecast key",
                "Forecast composite keys must be unique.",
                [DuplicateKeyCount = Table.RowCount(DuplicateKeys)]
            )
        else
            Typed
in
    Output

Date and Refresh Queries

Create a query named DimDate. Adjust FiscalStartMonth for a non-calendar fiscal year.

let
    FiscalStartMonth = 1,
    StartDate = #date(2024, 1, 1),
    CurrentDate = Date.From(DateTimeZone.FixedUtcNow()),
    EndDate = Date.EndOfYear(Date.AddYears(CurrentDate, 2)),
    DayCount = Duration.Days(EndDate - StartDate) + 1,

    Dates = Table.FromList(
        List.Dates(
            StartDate,
            DayCount,
            #duration(1, 0, 0, 0)
        ),
        Splitter.SplitByNothing(),
        {"Date"},
        null,
        ExtraValues.Error
    ),

    Typed = Table.TransformColumnTypes(
        Dates,
        {{"Date", type date}}
    ),

    AddedYear = Table.AddColumn(
        Typed,
        "CalendarYear",
        each Date.Year([Date]),
        Int64.Type
    ),

    AddedMonthNumber = Table.AddColumn(
        AddedYear,
        "MonthNumber",
        each Date.Month([Date]),
        Int64.Type
    ),

    AddedMonthStart = Table.AddColumn(
        AddedMonthNumber,
        "MonthStart",
        each Date.StartOfMonth([Date]),
        type date
    ),

    AddedMonthName = Table.AddColumn(
        AddedMonthStart,
        "MonthName",
        each Date.ToText([Date], "MMM"),
        type text
    ),

    AddedYearMonth = Table.AddColumn(
        AddedMonthName,
        "YearMonth",
        each Date.ToText([Date], "yyyy-MM"),
        type text
    ),

    AddedFiscalYear = Table.AddColumn(
        AddedYearMonth,
        "FiscalYear",
        each
            if Date.Month([Date]) >= FiscalStartMonth then
                Date.Year([Date])
            else
                Date.Year([Date]) - 1,
        Int64.Type
    ),

    AddedFiscalMonth = Table.AddColumn(
        AddedFiscalYear,
        "FiscalMonthNumber",
        each
            Number.Mod(
                Date.Month([Date]) - FiscalStartMonth,
                12
            ) + 1,
        Int64.Type
    )
in
    AddedFiscalMonth

Create a query named RefreshControl:

let
    Output = #table(
        type table [RefreshUtc = datetimezone],
        {{DateTimeZone.FixedUtcNow()}}
    )
in
    Output

Core DAX Measures

Create these measures in the Power BI semantic model. The example assumes expense values are positive and an unfavorable expense variance is Actual minus Budget.

Actual Amount =
SUM(FactActual[ReportAmount])

Budget Amount =
SUM(FactBudget[BudgetAmount])

Forecast Base Amount =
SUM(FactForecast[ForecastAmount])

Approved Forecast Adjustment =
CALCULATE(
    SUM(FactForecastAdjustment[AdjustmentAmount]),
    FactForecastAdjustment[ApprovalStatus] = "Approved"
)

Current Forecast =
[Forecast Base Amount] + [Approved Forecast Adjustment]

Variance Amount =
[Actual Amount] - [Budget Amount]

Variance Pct =
DIVIDE(
    [Variance Amount],
    ABS([Budget Amount])
)

Actual YTD =
TOTALYTD(
    [Actual Amount],
    DimDate[Date],
    "12/31"
)

Budget YTD =
TOTALYTD(
    [Budget Amount],
    DimDate[Date],
    "12/31"
)

Variance YTD =
[Actual YTD] - [Budget YTD]

Variance YTD Pct =
DIVIDE(
    [Variance YTD],
    ABS([Budget YTD])
)

Model Refresh UTC =
MAX(RefreshControl[RefreshUtc])

Exception Flag =
VAR AmountVariance =
    ABS([Variance Amount])
VAR BudgetValue =
    ABS([Budget Amount])
VAR PctVariance =
    ABS([Variance Pct])
VAR AmountThreshold =
    COALESCE(
        MAX(DimDepartment[VarianceAmountThreshold]),
        5000
    )
VAR PctThreshold =
    COALESCE(
        MAX(DimDepartment[VariancePctThreshold]),
        0.10
    )
VAR MinimumBudget =
    COALESCE(
        MAX(DimDepartment[MinimumBudgetForPct]),
        1000
    )
VAR AmountPassed =
    AmountVariance >= AmountThreshold
VAR PctPassed =
    IF(
        BudgetValue >= MinimumBudget,
        PctVariance >= PctThreshold,
        TRUE()
    )
RETURN
    IF(
        AmountPassed && PctPassed,
        1,
        0
    )

If the fiscal year does not end on December 31, replace the TOTALYTD year-end argument with the correct date. Validate this against manually calculated year-to-date totals.

DAX Exception Query for Power Automate

Place this query in the Power BI action that runs a query against the semantic model. Replace the fallback owner email.

EVALUATE
VAR ExceptionRows =
    FILTER(
        SUMMARIZECOLUMNS(
            DimDate[MonthStart],
            DimDepartment[ReportingDepartmentCode],
            DimDepartment[OwnerEmail],
            DimAccountGroup[AccountGroupCode],
            "ActualAmount", [Actual Amount],
            "BudgetAmount", [Budget Amount],
            "VarianceAmount", [Variance Amount],
            "VariancePct", [Variance Pct],
            "ExceptionFlag", [Exception Flag]
        ),
        [ExceptionFlag] = 1
    )
RETURN
    SELECTCOLUMNS(
        ExceptionRows,
        "ExceptionKey",
            FORMAT([MonthStart], "yyyyMM")
            & "|"
            & [ReportingDepartmentCode]
            & "|"
            & [AccountGroupCode],
        "MonthStart",
            FORMAT([MonthStart], "yyyy-MM-dd"),
        "DepartmentCode",
            [ReportingDepartmentCode],
        "AccountGroupCode",
            [AccountGroupCode],
        "OwnerEmail",
            COALESCE(
                [OwnerEmail],
                "YOUR_EMAIL_ADDRESS"
            ),
        "ActualAmount",
            ROUND([ActualAmount], 2),
        "BudgetAmount",
            ROUND([BudgetAmount], 2),
        "VarianceAmount",
            ROUND([VarianceAmount], 2),
        "VariancePct",
            ROUND([VariancePct], 4),
        "AbsoluteVariance",
            ROUND(ABS([VarianceAmount]), 2)
    )
ORDER BY
    [AbsoluteVariance] DESC

The Power BI connector normally exposes the first returned table as dynamic content. Interface labels can vary. Run a test query, inspect the action output, and pass the returned row array into Parse JSON.

Power Automate Parse JSON Schema

{
  "type": "array",
  "items": {
    "type": "object",
    "properties": {
      "[ExceptionKey]": {
        "type": "string"
      },
      "[MonthStart]": {
        "type": "string"
      },
      "[DepartmentCode]": {
        "type": "string"
      },
      "[AccountGroupCode]": {
        "type": "string"
      },
      "[OwnerEmail]": {
        "type": "string"
      },
      "[ActualAmount]": {
        "type": ["number", "null"]
      },
      "[BudgetAmount]": {
        "type": ["number", "null"]
      },
      "[VarianceAmount]": {
        "type": ["number", "null"]
      },
      "[VariancePct]": {
        "type": ["number", "null"]
      },
      "[AbsoluteVariance]": {
        "type": ["number", "null"]
      }
    },
    "required": [
      "[ExceptionKey]",
      "[MonthStart]",
      "[DepartmentCode]",
      "[AccountGroupCode]",
      "[OwnerEmail]",
      "[ActualAmount]",
      "[BudgetAmount]",
      "[VarianceAmount]",
      "[AbsoluteVariance]"
    ]
  }
}

If the connector returns aliases without square brackets, change the schema and expressions to match the tested output. Do not assume field labels without inspecting a real development run.

Useful Power Automate Expressions

Build the SharePoint OData filter for the current ExceptionKey:

concat(
  'ExceptionKey eq ''',
  items('Apply_to_each_exception')?['[ExceptionKey]'],
  ''''
)

Test whether no existing item was found:

equals(
  length(body('Get_existing_review')?['value']),
  0
)

Set a three-calendar-day due date:

addDays(utcNow(), 3)

Set priority using the absolute variance returned by DAX:

if(
  greaterOrEquals(
    float(items('Apply_to_each_exception')?['[AbsoluteVariance]']),
    25000
  ),
  'High',
  'Normal'
)

Confirm that the semantic model refreshed within three hours:

greaterOrEquals(
  ticks(outputs('Compose_RefreshUtc')),
  ticks(addHours(utcNow(), -3))
)

Enable trigger concurrency control so only one exception scan runs at a time. Configure the exception loop to run sequentially unless a tested locking strategy is added. Set connector retry policies for transient throttling and service failures, but do not retry invalid mappings or malformed source data.

Failure Handling and Operational Reliability

Idempotency is based on deterministic ExceptionKey and AdjustmentKey values. Power Automate checks the key before creating an item, and SharePoint unique-value enforcement provides a second control.

The Automation Failures list acts as a lightweight dead-letter queue. It stores the flow name, run identifier, source record key, failed action, sanitized error, retry count, status, owner, and recovery notes.

Finance performs a monthly reconciliation between accounting export totals, FactActual totals, and the accounting trial balance. It also compares the number of open Power BI exceptions with the number of open SharePoint reviews.

Manual recovery uses the source record key rather than creating a replacement record. The support owner corrects the cause, resets AutomationStatus to Ready for Retry, and runs the recovery flow. Every recovery attempt increments RetryCount.

A Complete Example

During the May 2026 close, the senior accountant uploads GL_Actuals_2026-05_Batch2.csv. The file contains valid headers and 4,912 transaction rows. The intake flow records the file and confirms that its hash has not already been processed.

One source row contains the following values:

{
  "EntryID": "GL-2026-051842",
  "TransactionDate": "2026-05-18",
  "AccountCode": "62410",
  "DepartmentCode": "OPS-NORTH",
  "Amount": 7800.00,
  "DocumentNumber": "AP-88421",
  "Description": "Expedited inbound freight",
  "SourceBatchID": "2026-05-CLOSE-2"
}

Power Query maps account 62410 to account group FREIGHT and maps OPS-NORTH to reporting department D20. The account sign multiplier is 1, so ReportAmount remains $7,800.

After all May transactions are aggregated, the Power BI model calculates the following for D20 and FREIGHT:

• Actual amount: $47,800
• Budget amount: $40,000
• Variance amount: $7,800 unfavorable
• Variance percentage: 19.5 percent
• Department amount threshold: $5,000
• Department percentage threshold: 10 percent

Both materiality conditions pass. The exception query returns key 202605|D20|FREIGHT. Power Automate does not find an existing SharePoint item, so it creates review VAR-202605-D20-FREIGHT. SharePoint returns item ID 184.

The department map assigns the review to [email protected]. The flow sets Status to New, Priority to Normal, and DueDate to three calendar days after creation. It creates the evidence folder and sends a Teams message and email containing the review link.

The operations manager reviews the underlying transactions and records the explanation that expedited inbound shipments caused by a supplier delay increased freight expense. The manager classifies the variance as Timing and proposes increasing the June freight forecast from $40,000 to $46,000.

The child Forecast Adjustment receives key 202605|D20|FREIGHT|202606. Power Automate calculates an AdjustmentAmount of $6,000 and routes it to the finance director. The executive threshold is not met, so no second approval is required.

The finance director approves the adjustment and adds a planning comment. Power Automate stores the approval outcome, response identifier, approver, comment, and timestamp. The adjustment status becomes Approved, and the parent review becomes Approved.

During the next Power BI refresh, the approved $6,000 adjustment is added to the June baseline forecast. The Forecast Outlook page shows the original $40,000 baseline, the $6,000 approved adjustment, and the resulting $46,000 current forecast.

If the Power BI refresh had failed, the approved SharePoint item would still exist, but it would not appear in the reporting model until a successful refresh. The stale-model check would stop the exception scan and alert the Power BI owner rather than processing old totals.

Implementation Cost

All amounts below are representative planning assumptions, not verified client results or vendor quotations. Software licensing depends on current Microsoft terms, geography, capacity, connector use, and existing agreements.

An organization with capable internal Power BI and Power Automate staff could replace some professional implementation hours with internal labor. That does not make the work costless. Data mapping, testing, training, governance, and maintenance still require time.

Estimated Time and Cost Savings

The calculation treats one departmental cost-center review pack as a workflow unit. Alder Creek Equipment has 18 such units per monthly reporting cycle.

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

18 × 55 ÷ 60 = 16.5 hours

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

(18 × 12 ÷ 60) + (2.7 × 20 ÷ 60) + 2 = 3.6 + 0.9 + 2 = 6.5 hours

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

16.5 – 6.5 = 10 hours

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

10 × $58 = $580

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

$580 – $175 = $405

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

$8,347 ÷ $405 = approximately 20.6 months

Recovered time does not automatically reduce payroll. It can represent additional finance capacity, faster monthly reporting, reduced overtime, fewer administrative tasks, less dependence on one analyst, or the ability to support higher reporting volume.

Non-financial benefits include clearer ownership, fewer email follow-ups, more consistent account mappings, improved audit evidence, quicker identification of missing data, approved forecast history, and better visibility into processing failures.

Readers should replace the volume, handling time, exception rate, labor rate, implementation cost, software allowance, and maintenance assumptions with their own figures. They should also account for seasonal close workload and the time required to resolve data-quality issues.

Adding AI to the Automation

AI is an optional enhancement added only after source validation, account mapping, calculations, workflow statuses, approvals, and monitoring operate reliably.

Potential AI uses include summarizing contributing transaction descriptions, suggesting an initial variance category, detecting missing information in explanations, comparing the current explanation with prior periods, and drafting a concise management summary.

AI is not appropriate for calculating the variance, deciding whether a threshold was exceeded, approving a forecast, changing an account mapping, closing a reporting period, or determining whether spending was permitted. Required fields, formulas, lookup tables, and explicit workflow rules handle those tasks more reliably.

The core automation provides consistent data, scheduled reporting, exception creation, ownership, reminders, approvals, and audit history. AI adds value only when employees must read and summarize unstructured transaction descriptions or narrative notes.

The Recommended AI Enhancement

The recommended enhancement drafts a variance summary from approved reporting values and a restricted set of transaction descriptions. The department manager must review, edit, and confirm the draft before it becomes the official explanation.

• Trigger: A new Variance Review is created and the transaction-summary input has been prepared.
• AI input: Exception ID, month, department, account group, actual, budget, variance, top contributing transactions, and prior approved explanation when permitted.
• System instruction: Produce a factual draft without inventing causes or approvals.
• Expected output: Structured JSON containing a draft summary, suggested category, missing-information list, confidence, and review warning.
• Validation: Parse JSON, verify allowed categories, enforce length limits, and reject missing fields.
• Record update: Store the text in AISummaryDraft, not in the approved Explanation field.
• Human review: Department owner confirms or rewrites the draft.
• Low confidence: Route directly to manual review without presenting the draft as reliable.
• Prohibited data: Payroll details, bank information, tax identifiers, medical information, personal addresses, and unrestricted vendor data.
• Logging: Store model identifier, prompt version, source hash, timestamp, token usage, and review result.
• Failure behavior: Continue the normal human review without AI.

Reusable System Instruction

You assist a finance team with drafting budget variance explanations.

Use only the information supplied by the user. Do not invent causes, approvals, commitments, accounting conclusions, or future events.

The values are expense amounts. A positive variance means actual expense exceeded budget. A negative variance means actual expense was below budget.

Return valid JSON that matches the supplied schema.

The draft_summary must:
1. State the reporting period and account group.
2. State the actual, budget, amount variance, and percentage variance.
3. Describe only causes directly supported by the supplied transaction descriptions or prior approved notes.
4. Identify missing information instead of guessing.
5. Remain under 120 words.

The suggested_category must be one of:
Timing, Volume, Price, One-Time, Coding, Other, Insufficient Information.

Never approve a forecast, close a review, change a mapping, or state that management accepted the explanation.

Reusable User Prompt

Exception ID: {{EXCEPTION_KEY}}
Reporting month: {{MONTH_START}}
Department: {{DEPARTMENT_CODE}}
Account group: {{ACCOUNT_GROUP_CODE}}
Actual amount: {{ACTUAL_AMOUNT}}
Budget amount: {{BUDGET_AMOUNT}}
Variance amount: {{VARIANCE_AMOUNT}}
Variance percentage: {{VARIANCE_PCT}}

Top contributing transactions:
{{TOP_TRANSACTION_SUMMARY}}

Prior approved explanation, if available:
{{PRIOR_APPROVED_EXPLANATION}}

Draft a factual variance summary. List any information a manager must confirm before the summary can be accepted.

Expected Structured Output

{
  "draft_summary": "May freight expense was 47800 against a budget of 40000, producing a 7800 unfavorable variance of 19.5 percent. The supplied transaction descriptions show expedited inbound freight as a major contributor. A manager must confirm whether the expedited shipments were caused by timing, supplier delay, or another operational event.",
  "suggested_category": "Insufficient Information",
  "missing_information": [
    "Confirmed operational cause",
    "Whether the cost shifts spending from a later month",
    "Whether the remaining forecast should change"
  ],
  "confidence": 0.71,
  "human_review_required": true
}

Optional Power Automate HTTP Configuration

If the organization approves the OpenAI API and the required Power Automate HTTP capability, configure a POST request to https://api.openai.com/v1/responses. Replace the model and credential placeholders. Store the API key through approved secret management rather than plain-text flow configuration.

{
  "model": "YOUR_APPROVED_MODEL",
  "instructions": "You assist a finance team with drafting budget variance explanations. Use only supplied information. Do not invent causes, approvals, accounting conclusions, or future events. Return JSON matching the schema.",
  "input": [
    {
      "role": "user",
      "content": [
        {
          "type": "input_text",
          "text": "@{outputs('Compose_AI_User_Prompt')}"
        }
      ]
    }
  ],
  "text": {
    "format": {
      "type": "json_schema",
      "name": "variance_review_draft",
      "strict": true,
      "schema": {
        "type": "object",
        "properties": {
          "draft_summary": {
            "type": "string"
          },
          "suggested_category": {
            "type": "string",
            "enum": [
              "Timing",
              "Volume",
              "Price",
              "One-Time",
              "Coding",
              "Other",
              "Insufficient Information"
            ]
          },
          "missing_information": {
            "type": "array",
            "items": {
              "type": "string"
            }
          },
          "confidence": {
            "type": "number",
            "minimum": 0,
            "maximum": 1
          },
          "human_review_required": {
            "type": "boolean"
          }
        },
        "required": [
          "draft_summary",
          "suggested_category",
          "missing_information",
          "confidence",
          "human_review_required"
        ],
        "additionalProperties": false
      }
    }
  }
}

Set the Content-Type header to application/json and the Authorization header to Bearer YOUR_API_KEY. Mark the request and response actions as secure. Use exponential retry for HTTP 429 and 5xx responses, but do not retry an invalid request indefinitely.

Parse the returned structured text, validate its schema, and store it only if the SourceHash still matches the review. If confidence is below 0.70, the response is malformed, or the service is unavailable, set AIStatus to Manual Review and continue without a draft.

Before deployment, test the current API response structure in the approved environment. API versions and connector output shapes can change, so the production flow should not rely on an untested response path.

Benefits of the AI Enhancement

• Reduces time spent reading repetitive transaction descriptions.
• Produces a consistent starting structure for variance explanations.
• Highlights missing operational information before approval.
• Suggests a category without changing the official classification.
• Supports concise executive summaries from longer transaction lists.
• Improves review consistency when multiple department managers write explanations.

These benefits are separate from the core automation. Power BI and Power Automate already provide calculation, exception detection, ownership, reminders, approval evidence, and reporting. AI primarily reduces reading and drafting effort for unstructured text.

What Remains Rule-Based or Human-Controlled

Estimating the Additional Value of AI

The AI estimate uses 24 detailed variance lines per month. These are underlying exception lines, not the 18 departmental review packs used in the core savings calculation.

Expected successful lines are 24 × 95 percent = 22.8 lines.

Gross time recovered is 22.8 × 4 minutes ÷ 60 = 1.52 hours.

Correction time is 24 × 20 percent × 3 minutes ÷ 60 = 0.24 hours.

Net additional capacity is approximately 1.52 – 0.24 = 1.28 hours per month.

At a loaded labor cost of $58 per hour, the representative labor value is $74.24. After the $8 usage allowance, the net estimated monthly value is approximately $66.24.

This estimate does not assume AI eliminates review, correction, or service failure. If transaction descriptions are short or exception volume is low, the enhancement may not justify its governance and maintenance effort.

Testing Checklist

Use sample data and non-production accounts before processing real financial information.

Ongoing Maintenance

The FP&A analyst is the primary business owner. The finance systems owner is responsible for model and flow maintenance, while the senior accountant is the backup operational owner.

Documentation is updated whenever a field, mapping rule, threshold, workflow status, approval limit, connector, or source location changes. Former users are removed from SharePoint groups, Power BI roles, flow ownership, approval routing, and distribution lists.

Power Query and DAX tests should be rerun after accounting export changes. The finance team should not wait for the next close to discover that a source column was renamed.

When to Move to Dedicated Software

This implementation can remain appropriate while the model, workflow, and governance effort stay manageable. It should not be replaced merely because a dedicated planning platform exists.

Upgrade evaluation becomes reasonable when:

• Transaction or planning volume causes unacceptable refresh duration.
• Hundreds of users require complex write-back permissions.
• Forecasting requires multiple scenarios, allocations, drivers, and formal version control.
• Budget submissions require a customer-facing or supplier-facing portal.
• Multiple entities, currencies, or accounting systems create complex consolidation requirements.
• Formal regulatory controls require stronger segregation of duties or certified workflows.
• Spreadsheet maintenance and mapping exceptions consume excessive finance time.
• Approval workflows require complex parallel routing and delegated authorities.
• The business needs mobile or offline planning entry.
• Power BI model size, refresh frequency, or performance becomes difficult to manage.
• The company requires vendor-supported financial close, consolidation, or enterprise performance management.
• Security risk increases because more sensitive transaction detail enters the reporting model.

Possible next-stage categories include dedicated FP&A platforms, enterprise performance-management software, financial data warehouses, and accounting-native analytics. The existing mappings, data definitions, exception history, and approval rules can provide useful requirements for that transition.

Implementation Checklist

• Confirm monthly, year-to-date, forecast, and variance requirements.
• Document transaction volume, reporting units, owners, and materiality policies.
• Select Excel, Power Query, Power BI, Power Automate, SharePoint, and notification tools.
• Create development and production accounts, workspaces, folders, and lists.
• Assign primary and backup owners.
• Configure least-privilege permissions and report audiences.
• Define actual, budget, forecast, mapping, review, and adjustment fields.
• Create unique keys, indexes, constraints, and default statuses.
• Standardize accounting-export and planning-intake formats.
• Build and test Power Query transformations.
• Map source accounts and departments to reporting dimensions.
• Build the semantic model and validated DAX measures.
• Configure scheduled refresh and freshness monitoring.
• Build field mappings between Power BI, Power Automate, and SharePoint.
• Create idempotent exception-detection automation.
• Configure ownership, reminders, escalation, and reassignment.
• Configure sequential forecast approvals and delegation.
• Create evidence folders, naming conventions, versioning, retention, and archive rules.
• Build executive, department, review, data-quality, and automation-health views.
• Configure failure scopes, retries, logs, and manual recovery.
• Test security, row-level access, former-user removal, and shared-link restrictions.
• Validate source totals and reporting totals with sample data.
• Complete user acceptance testing and a limited pilot.
• Document deployment, rollback, recovery, and support procedures.
• Replace representative implementation-cost assumptions with organization-specific figures.
• Replace savings assumptions with measured volume and handling time.
• Add AI only after the core process operates reliably.
• Require human review of every AI-generated draft.
• Assign a maintenance owner and recurring review schedule.
• Document the volume, control, security, and workflow conditions that would justify dedicated software.