Automating Approved Board Reports with Power BI

The Business Situation

Cedar Vale Technical Services is a fictional 95-person B2B field services and equipment maintenance company. Its board receives a quarterly package covering financial performance, sales, operations, customer retention, workforce indicators, strategic initiatives, risks, and management actions.

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 finance and analytics team owns the reporting process. Five departmental data owners contribute information from Finance, Sales, Operations, Customer Success, and People. Department executives approve their submissions. The CFO reviews the consolidated financial package, while the CEO and board liaison control final release.

Each quarter, the company collects approximately 32 board metrics and 15 written commentary sections. The source data already exists in departmental Excel workbooks, accounting exports, operational reports, and Power BI dashboards. However, there was no controlled workflow connecting the source files, approvals, reporting model, document generation, and distribution.

The finance analyst spent much of the reporting cycle copying values into a master spreadsheet, reconciling conflicting versions, requesting missing explanations, rebuilding charts, and transferring the results into presentation material. Collectively, the process consumed approximately 75 staff hours per quarter.

The reporting process needed to change because the business was growing, reporting expectations were increasing, and the existing process depended heavily on one analyst. Executives also wanted clearer evidence showing who submitted, reviewed, approved, changed, and released each part of the package.

The Existing Process

The original process followed the same broad sequence each quarter:

1. The finance analyst emailed five departmental data owners with a reporting deadline and attached the previous quarter’s spreadsheet.
2. Each department copied the old workbook, replaced selected values, and emailed the revised file back.
3. The analyst copied figures into a master Excel workbook and manually checked totals against departmental reports.
4. Questions and corrections moved through email threads, frequently producing several files with similar names.
5. Departmental commentary arrived through spreadsheets, email messages, and meeting notes.
6. The analyst rebuilt charts and tables, then transferred them into the board presentation.
7. The CFO reviewed a PDF or presentation attachment and returned changes by email.
8. The final package was renamed, emailed to a distribution list, and stored in a shared folder.

The central problem was not that Excel or Power BI were unsuitable. The problem was that the tools were disconnected. The company needed controlled inputs, deterministic validation, explicit approvals, a governed reporting model, and an automated release process.

What the New System Needed to Do

The design also needed to preserve existing departmental Excel workflows. The company did not want a reporting project to become a replacement of every operational system. Departments could continue calculating their numbers in existing systems, but board-report inputs had to conform to one governed schema.

Implementation Approaches Considered

Improving the manual process

Standard file names and a better checklist would reduce some confusion, but they would not reliably prevent unapproved metrics from entering the package. The finance analyst would still rebuild visuals and preserve approval evidence manually.

Using the existing Microsoft environment

This approach retained familiar Excel inputs while adding SharePoint records, Power Automate workflows, Power Query extraction, and Power BI document generation. It provided sufficient control for the company’s volume without creating a new application.

Using a no-code database

A no-code database could provide stronger form controls and relational records. It was less suitable because departmental users already worked in Excel, some metric schedules were wide and calculation-heavy, and the company wanted to minimize additional platforms.

Building a custom application

A custom application would allow precise validation and user experiences, but quarterly reporting volume did not justify the build, testing, security, and support burden.

Purchasing dedicated software

Dedicated board-management software may become appropriate if the company needs formal electronic board books, complex committee permissions, digital signatures, offline access, or regulated retention. For the representative scenario, the immediate problem was controlled report production rather than full board administration.

The Selected Solution

Cedar Vale selected a Microsoft-based workflow connecting Excel, SharePoint, Power Query, Power BI, and Power Automate. The company already used Microsoft 365 and Power BI, so the implementation could retain familiar identities, files, and analytics.

Excel remained the intake tool because data owners needed to review calculations and supporting schedules before submission. SharePoint became the system of record because an Excel workbook alone was not an appropriate workflow database.

Power Query loaded only files that had passed validation and approval. Power BI generated both the management dashboard and a paginated board package. Power Automate coordinated the workflow from submission through secure release.

The automation removed copying into a master workbook, repeated chart rebuilding, manual approval chasing, package renaming, and attachment distribution. It did not automate management judgment. Department executives still approved their numbers, the CFO still approved the consolidated financial presentation, and the board liaison still authorized release.

System Architecture and Data Flow

• Intake: Department-specific Excel workbooks containing named tables for metrics and commentary
• System of record: SharePoint Lists for cycles, submissions, packages, ownership, distribution, and errors
• Automation layer: Power Automate cloud flows using Microsoft 365 and Power BI connections
• Document storage: Restricted SharePoint libraries with separate draft, approved, review, and released areas
• Notifications: Teams and Outlook messages containing record links rather than board-package attachments
• Reporting: Power Query, a Power BI semantic model, an operational dashboard, and a paginated report
• AI layer: Optional controlled drafting of commentary from approved inputs, always subject to human review

1. Create the reporting cycle. The analytics manager creates a SharePoint cycle record with a cycle key, period end, cutoff date, expected departments, and review dates. Power Automate creates the required folder structure and departmental submission records.
2. Prepare departmental inputs. Each data owner updates an assigned Excel workbook. The workbook contains controlled metric codes, numeric values, dates, commentary, and row-level validation results.
3. Submit a snapshot. The data owner changes the SharePoint submission status to Submitted. The trigger receives the submission identifier, cycle, department, workbook identifier, owner, and revision.
4. Validate the workbook. Power Automate copies the draft to a staging area, reads the named Excel tables, compares them with the metric catalog, and records validation errors. Invalid submissions return to the owner.
5. Approve the departmental input. A finance or analytics reviewer checks reconciliation and completeness. The department executive then approves the business content. Approval responses and comments are written to the submission record.
6. Publish the approved snapshot. The staged workbook is moved to the restricted Approved Inputs library. The approved file name and SharePoint identifier are stored in the submission record. Prior active revisions are marked Superseded.
7. Confirm cycle readiness. A scheduled or manually initiated orchestration flow counts active approved submissions and compares them with the expected department list. Missing departments keep the cycle in Collecting or Exception status.
8. Refresh the reporting model. The flow sets a unique refresh token on the cycle, marks it as the current package, and requests a Power BI semantic-model refresh.
9. Verify the refresh. Power Automate runs a small DAX query against the semantic model until the token returned by Power BI matches the token stored in SharePoint. This prevents a stale report from being exported.
10. Generate the package. Power Automate exports the Power BI paginated report to PDF, creates a versioned file in the Review library, and stores the returned SharePoint file identifier and link.
11. Review the package. The CFO reviews the exact PDF file. If approved, the board liaison performs the release review. Rejection sends the package to Rework without altering the reviewed file.
12. Release and distribute. The approved PDF is copied to the Released library. The flow updates permissions, records the release event, and sends named recipients a secure link.
13. Handle failures. A failed validation, refresh, export, file operation, approval, or notification sets the relevant record to Exception, increments the retry count, and creates an error-log entry for the system owner.

Data Structure

Reporting Cycles

Submission Register

Excel Metric and Commentary Tables

Package Versions

The Package Versions list contains PackageKey, CycleKey, VersionNumber, Status, GeneratedAtUTC, GeneratedBy, PowerBIRefreshToken, SharePointFileIdentifier, DocumentLink, CFOApprovalStatus, ReleaseApprovalStatus, ReleasedAtUTC, DistributionStatus, RetryCount, ErrorMessage, and Notes.

A reporting cycle has many departmental submissions and package versions. A submission has many metric and commentary rows inside its approved workbook. MetricCode relates each fact row to the Metric Catalog. CycleKey relates metrics, commentary, submissions, packages, and control records.

Workflow Statuses and Ownership

A record moves backward when validation fails, an approver requests more information, a package is rejected, or a newer approved revision replaces an earlier one. Automation cannot convert a rejected item directly to Approved. A corrected file must be submitted as a new revision so that the audit trail remains intact.

Step-by-Step Implementation

Step 1: Prepare the Accounts and Permissions

The implementation requires Microsoft 365 accounts with access to Excel, SharePoint, Power Automate, Teams or Outlook, and the required Power BI features. The Power BI environment must support semantic-model refresh, paginated reports, and export through Power Automate. Licensing and capacity requirements should be confirmed against the organization’s tenant because they vary by environment and deployment model.

1. Create separate SharePoint sites or clearly separated libraries for development, testing, and production.
2. Create development and production Power BI workspaces. Limit publishing permissions to analytics administrators and the automation identity.
3. Create a dedicated automation identity or otherwise use organization-owned connections that are not dependent on one employee’s personal account. License and govern the identity according to Microsoft requirements.
4. Create Power Automate connections for SharePoint, Excel Online, Power BI, Approvals, Teams, and Outlook.
5. Give the automation identity read and write access to the control lists, staging library, Approved Inputs library, Review library, and Released library.
6. Give departmental data owners edit access only to their draft folders and submission records.
7. Give finance reviewers read access to staged snapshots and update access to review fields.
8. Give department executives read access to the submitted snapshot associated with their approval.
9. Give the CFO and board liaison read access to review packages.
10. Give named board recipients read-only access to released packages. Disable anonymous sharing.
11. Create at least one test identity for each role so user acceptance testing does not depend on administrator permissions.

Use test workspaces, test libraries, synthetic metrics, and test users before connecting production data. Power Automate flows should initially be disabled or restricted to test cycle keys.

Step 2: Build the Intake

Create one controlled Excel template for all departments, then issue a department-specific copy. Store the workbook in the department’s SharePoint Draft Inputs folder. The workbook must remain in SharePoint because the Excel Online connector needs a cloud file and named Excel tables.

Use Excel data validation lists for MetricCode, SectionCode, RiskLevel, and units where users may select values. Protect formula cells, identifiers, and lookup ranges while leaving input cells editable.

Examples of row-level validation formulas are:

=IF([@ActualValue]="","Missing Actual",IF([@SourceAsOfDate]="","Missing Date","OK"))

=IF(COUNTIFS(tblMetrics[MetricCode],[@MetricCode])>1,"Duplicate","OK")

=IF(AND(ABS(([@ActualValue]-[@PlanValue])/[@PlanValue])>0.1,[@DataQualityNote]=""),"Review Note Required","OK")

The workbook validation is a convenience for data owners, not the final control. Power Automate repeats critical validation after creating a read-only snapshot.

The SharePoint submission record acts as the formal intake event. Required fields are CycleKey, DepartmentCode, Revision, OwnerEmail, DraftFileIdentifier, FinanceReviewerEmail, DepartmentApproverEmail, and Status.

On submission, display a confirmation that the workbook will be copied to a snapshot, validated, and routed for approval. Explain that later corrections require a new revision. The intake notice should also prohibit individual employee records, customer credentials, banking details, and other information not intended for board reporting.

Spam prevention is primarily identity-based because only authenticated employees can create or edit submission records. Duplicate prevention uses a unique SubmissionKey and a check for another active submission with the same cycle, department, and revision.

Step 3: Create the System of Record

Create the following SharePoint Lists:

• Reporting Cycles
• Submission Register
• Metric Catalog
• Commentary Catalog
• Approver Directory
• Package Versions
• Distribution Register
• Automation Error Log

Use internal column names without spaces where possible, even if display labels later contain spaces. This produces more stable Power Query and Power Automate references.

Configure SubmissionKey in the Submission Register to enforce unique values. Add indexes to CycleKey, DepartmentCode, Status, OwnerEmail, DueDate, and IsActiveApproval. Add indexes to CycleKey and Status in the Package Versions list.

Create the following SharePoint document libraries and folders:

• Board Reporting Templates
• Draft Inputs divided by cycle and department
• Submission Staging divided by cycle
• Approved Inputs divided by cycle
• Board Packages with Review, Released, Withdrawn, and Archive folders

Enable version history. Prevent departmental data owners from writing to Submission Staging and Approved Inputs. The automation identity creates and moves files in those libraries.

Use these identifier conventions:

CycleKey: BR-YYYY-QN
SubmissionKey: BR-YYYY-QN-DEPT-rNN
PackageKey: PKG-BR-YYYY-QN-vNN
Approved file: BoardInput_BR-YYYY-QN_DEPT_rNN.xlsx
Review package: Board_Package_BR-YYYY-QN_vNN.pdf

Create filtered SharePoint views for My Draft Submissions, Awaiting Finance Review, Awaiting Department Approval, Validation Failures, Overdue Submissions, Active Approved Inputs, Package Review, Automation Exceptions, and Recently Released Packages.

Step 4: Connect the Tools

Use Microsoft organizational authentication for SharePoint, Excel, and Power BI connections. Do not place passwords or tokens in flow variables. Connections should be organization-owned and included in documented connection references if the flows are packaged in a Power Platform solution.

Step 5: Build the Core Automation

Automation A: Validate and approve a departmental submission

• Trigger: Submission Register item changes to Submitted while AutomationStatus is Pending
• Conditions: Cycle is open, cutoff has not passed or an authorized exception exists, identifiers are valid, and the revision is not duplicated
• Actions: Lock the record, copy the workbook, read tables, validate rows, request approvals, publish the approved snapshot, and update the record
• Fields updated: Status, ApprovalStatus, SnapshotFileIdentifier, ApprovedFileName, ApprovedDocumentLink, IsActiveApproval, LastAutomationRun, RetryCount, and ErrorMessage
• Notification: Owner, finance reviewer, and department approver receive role-specific links
• Exception: Validation or technical failure moves the record to Validation Failed or Exception

Configure trigger concurrency to one where practical for the submission flow. Add a trigger condition requiring both Submitted and Pending. The first action updates AutomationStatus to Running and assigns a generated ProcessingToken. A duplicate trigger then fails the Pending condition.

The exact action order is:

1. Read the complete submission item and related cycle.
2. Confirm that CycleKey, DepartmentCode, Revision, owner, reviewers, file identifier, and due date are present.
3. Generate the SubmissionKey if it is not already assigned.
4. Query for another record with the same SubmissionKey. Stop if one exists.
5. Check the cycle status and cutoff. Route a late item to manual review unless an approved exception is recorded.
6. Copy the draft workbook to Submission Staging using the generated file name.
7. Store the returned staged file identifier on the submission.
8. Read tblSubmissionMeta, tblMetrics, tblCommentary, and tblValidationSummary from the staged copy.
9. Query the Metric Catalog for active metrics assigned to the department.
10. For each required metric, confirm that exactly one workbook row exists.
11. Validate numeric fields, source dates, required notes, allowed codes, duplicate rows, and commentary requirements.
12. If validation errors exist, write a concise error list to the submission, set Validation Failed, and notify the owner.
13. If validation succeeds, set Finance Review and request the finance reviewer’s approval.
14. If finance returns the item, set Rejected or Validation Failed according to the response and require a comment.
15. If finance approves, set Department Approval and request the department executive’s approval.
16. If the executive rejects, set Rejected and notify the owner.
17. If approved, move or copy the staged snapshot to Approved Inputs.
18. Store the approved file name, identifier, and link.
19. Find prior active approved submissions for the same cycle and department and mark them Superseded.
20. Set the new record to Approved, ApprovalStatus to Approved, IsActiveApproval to Yes, and AutomationStatus to Complete.
21. Record the completion timestamp and notify the analytics manager.

Automation B: Refresh and generate the board package

• Trigger: Analytics manager requests package generation, or a scheduled readiness flow finds all required approved inputs
• Conditions: Expected submission count matches active approvals, no unresolved critical errors exist, and no package generation is already running
• Actions: Set the current cycle, generate a refresh token, refresh Power BI, verify the token, export the report, create a versioned file, and request approval
• Fields updated: Cycle Status, RefreshToken, PackageVersion, PackageLink, AutomationStatus, LastAutomationRun, and package approval fields
• Notification: Analytics manager receives generation status; CFO receives the review request
• Exception: A stale model, refresh failure, export timeout, or file error sends the cycle to Exception

The exact action order is:

1. Set cycle AutomationStatus to Running.
2. Retrieve active approved submissions for the cycle.
3. Confirm that each expected department appears exactly once.
4. Confirm that no submission has Validation Failed, Rejected, or Exception as its active state.
5. Set IsCurrentPackage to No on any prior cycle.
6. Set IsCurrentPackage to Yes on the target cycle.
7. Increment PackageVersion.
8. Generate a GUID and store it as RefreshToken.
9. Set cycle Status to Model Refreshing.
10. Request a refresh of the Power BI semantic model.
11. Wait for a short configured interval, then run the refresh-verification DAX query.
12. Repeat the query until Power BI returns the same RefreshToken or the polling timeout is reached.
13. Confirm that the model’s active submission count matches the SharePoint count.
14. Export the Power BI paginated report in PDF format.
15. Create the PDF in the cycle’s Review folder using the current PackageVersion.
16. Create a Package Versions item and store the SharePoint file identifier and document link.
17. Set the cycle to Package Review.
18. Request the CFO approval against the generated file.

Automation C: Release and distribute the approved package

• Trigger: CFO approves a package version
• Conditions: The package is the current version, the PDF exists, and the cycle has no unresolved critical exception
• Actions: Request final release approval, copy the PDF to Released, restrict access, record recipients, and send secure links
• Fields updated: ReleaseApprovalStatus, ReleasedAtUTC, DistributionStatus, DocumentLink, and cycle Status
• Notification: Named board recipients and approved executives receive a link
• Exception: Rejection sends the package to Rework; notification failure does not invalidate the approved document

The release flow must reference the exact SharePoint file identifier from the Package Versions record. It should never identify the document by searching for the newest file name alone.

After final approval, copy the review PDF to the Released folder, apply the approved access model, and store the released file identifier. Send links only after confirming the destination file exists and the current user group has permission.

Step 6: Add Approvals, Reminders, and Escalations

Use sequential approval for each submission because financial validation should occur before executive content approval. Departmental submissions can progress in parallel because each submission runs independently.

The Approver Directory should contain primary approver, delegate, effective start, effective end, and active status. Before creating an approval, the flow checks for an active delegate. If neither primary nor delegate is available, the record enters Exception rather than assigning the approval to an arbitrary user.

Require comments for rejection and return-for-information outcomes. Store the response, responder identity, response timestamp, and comments in SharePoint. Do not rely solely on an approval email as long-term evidence.

A scheduled reminder flow runs each morning. It finds incomplete submissions with due dates in seven, three, and one day, then sends reminders only if the corresponding reminder timestamp is blank. Overdue submissions receive a daily reminder and an escalation after the configured grace period.

Approval timeouts should be shorter than the platform’s maximum flow-run duration. If a business process may wait for several weeks, use a state-based design with separate flows rather than leaving one cloud-flow run open indefinitely.

Step 7: Add Documents and File Management

Power Automate creates the folder structure when the reporting cycle opens:

Draft Inputs/BR-2026-Q2/FIN
Draft Inputs/BR-2026-Q2/SAL
Draft Inputs/BR-2026-Q2/OPS
Draft Inputs/BR-2026-Q2/CS
Draft Inputs/BR-2026-Q2/PPL
Submission Staging/BR-2026-Q2
Approved Inputs/BR-2026-Q2
Board Packages/BR-2026-Q2/Review
Board Packages/BR-2026-Q2/Released
Board Packages/BR-2026-Q2/Withdrawn

Use explicit revision and package numbers in file names. Never replace an approved input or reviewed package with a different file that has the same name.

Draft files retain normal SharePoint version history. Staged snapshots are writeable only by the automation identity. Approved files are read-only for departmental users. Released package files are read-only for board recipients.

If an approved input requires correction, create revision two, route it through the complete approval process, and mark revision one Superseded. The old file remains available for audit purposes but Power Query reads only the active approved file identified in the Submission Register.

Configure organizational retention according to legal, financial, tax, corporate-record, and privacy requirements. Do not choose a retention period solely because it is convenient for the automation.

Validate file type and size before copying. Reject password-protected or corrupted workbooks because Power Automate and Power Query cannot reliably read them. If the staged workbook is temporarily locked, apply a bounded retry policy. After the retry limit, preserve the submission record and route it to the manual-review queue.

Step 8: Add Reporting and Operational Views

Create SharePoint operational views for:

• New and unprocessed submissions
• Work awaiting finance review
• Work awaiting department approval
• Overdue and incomplete submissions
• Validation failures and technical exceptions
• Rejected submissions
• Submissions by owner and department
• Upcoming cutoff and review dates
• Recently approved inputs
• Package versions awaiting action
• Automation failures and retry count
• Manual-review queue

Create a Power BI operational report using SharePoint control data. Suggested measures include submission count by status, percentage of required departments approved, average review time, overdue count, package generation duration, exception count, and refresh age.

The board report should contain only the current approved reporting cycle. Suggested pages include:

1. Cover page, period, package version, confidentiality notice, and table of contents
2. Executive summary
3. Financial performance
4. Revenue and pipeline
5. Operational delivery and capacity
6. Customer retention and service quality
7. Workforce indicators using aggregated data
8. Strategic initiative status
9. Material risks and management actions
10. Definitions and approved data notes

Power Query refreshes when package generation begins. The operational dashboard may refresh on a separate schedule, but a package is generated only after the package flow verifies its unique refresh token.

The analytics manager owns dashboard definitions and alert thresholds. Material-variance thresholds should be stored in the Metric Catalog rather than embedded independently in multiple visuals and flows.

Step 9: Add Security and Governance Controls

• Apply least-privilege permissions to lists, workspaces, libraries, and flow connections.
• Use security groups rather than repeatedly granting individual access where possible.
• Prevent anonymous sharing and restrict released packages to named identities.
• Require multifactor authentication and follow the organization’s conditional-access policy.
• Keep credentials in managed connections or approved secret stores, not workbook cells or flow variables.
• Restrict Power BI workspace publishing and report export permissions.
• Use aggregated People metrics and suppress small groups where disclosure risk exists.
• Exclude customer credentials, employee-level compensation, health information, and unnecessary personal data.
• Preserve approval identities, comments, timestamps, file identifiers, and package versions as audit evidence.
• Remove former employees from SharePoint groups, Power BI workspaces, flow ownership, and distribution groups promptly.
• Review external guest access before each release cycle.
• Back up configuration exports, templates, Power Query definitions, report files, and flow documentation.
• Define retention and disposal rules with legal, finance, privacy, and records-management stakeholders.

For optional AI use, send only approved and minimized inputs. Disable use of public consumer AI tools for board data. Human approval is required before AI-produced commentary can enter a package.

Step 10: Deploy and Test

1. Build the SharePoint lists and libraries in a development site.
2. Create a development Power BI workspace and connect it only to test files.
3. Create test cycles, submissions, metric catalogs, and approver records.
4. Use synthetic data that exercises positive, negative, percentage, date, and blank-value cases.
5. Test each flow independently before enabling the end-to-end sequence.
6. Confirm the automation identity can read and write only the intended resources.
7. Conduct user acceptance testing with one data owner, reviewer, executive approver, analytics user, and board-recipient test identity.
8. Pilot one complete reporting cycle in parallel with the established reporting process.
9. Reconcile every board metric and commentary section between the old and new packages.
10. Obtain formal acceptance from finance, analytics, IT, privacy, and the executive sponsor.
11. Move flows through a controlled deployment process and reconnect production connection references.
12. Activate the production flows in sequence: intake, reminders, package generation, release, and error monitoring.
13. Document the rollback method. A rollback should stop new automation, preserve submitted files and logs, and allow finance to complete the package using approved snapshots.
14. Publish a short operating guide covering submissions, corrections, approvals, exceptions, and support contacts.

Code and Configuration

The core solution uses native Power Automate actions, Power Query M, and DAX. It does not require a separate server or custom application.

Power Query Parameters

In Power BI Desktop, open the Power Query editor and create the following two text parameters. Replace the placeholder values with the production SharePoint site and approved-library path fragment.

"https://YOUR_TENANT.sharepoint.com/sites/BoardReporting"
meta
[
    IsParameterQuery = true,
    Type = "Text",
    IsParameterQueryRequired = true
]

Name this query pSiteUrl.

"/Approved Inputs/"
meta
[
    IsParameterQuery = true,
    Type = "Text",
    IsParameterQueryRequired = true
]

Name this query pApprovedPathFragment.

Reusable Excel Table Function

Create a blank query named fnGetNamedTable, open its advanced editor, and paste the following complete M function. It opens an Excel binary and returns one named table. It deliberately raises a refresh error if the required table is missing or duplicated.

(file as binary, tableName as text) as table =>
let
    Workbook = Excel.Workbook(file, null, true),
    Matches = Table.SelectRows(
        Workbook,
        each [Kind] = "Table" and [Item] = tableName
    ),
    Result =
        if Table.RowCount(Matches) = 1 then
            Matches{0}[Data]
        else
            error Error.Record(
                "InvalidWorkbookStructure",
                "The workbook must contain exactly one table named " & tableName & ".",
                [RequestedTable = tableName, MatchCount = Table.RowCount(Matches)]
            )
in
    Result

Approved Files Query

Create a query named ApprovedFiles. This query reads Excel files from the Approved Inputs library and excludes temporary or hidden files.

let
    Source = SharePoint.Files(pSiteUrl, [ApiVersion = 15]),
    Filtered = Table.SelectRows(
        Source,
        each
            Text.Contains([Folder Path], pApprovedPathFragment)
            and [Extension] = ".xlsx"
            and not Text.StartsWith([Name], "~$")
            and Record.FieldOrDefault([Attributes], "Hidden", false) = false
    ),
    Selected = Table.SelectColumns(
        Filtered,
        {"Name", "Folder Path", "Content", "Date modified"}
    ),
    Renamed = Table.RenameColumns(
        Selected,
        {
            {"Name", "SourceFileName"},
            {"Folder Path", "SourceFolderPath"},
            {"Date modified", "SourceModifiedUTC"}
        }
    )
in
    Renamed

Active Submission Query

Create a query named ActiveSubmissions. The SharePoint list fields should use the internal names shown in the code. The query treats the Submission Register, not workbook metadata, as the approval authority.

let
    Lists = SharePoint.Tables(pSiteUrl, [ApiVersion = 15]),
    SubmissionItems = Lists{[Title = "Submission Register"]}[Items],
    Selected = Table.SelectColumns(
        SubmissionItems,
        {
            "Title",
            "CycleKey",
            "DepartmentCode",
            "Revision",
            "Status",
            "IsActiveApproval",
            "ApprovedFileName",
            "ApprovedDocumentLink",
            "Modified"
        }
    ),
    Renamed = Table.RenameColumns(
        Selected,
        {
            {"Title", "SubmissionKey"},
            {"Modified", "SubmissionUpdatedUTC"}
        }
    ),
    Typed = Table.TransformColumnTypes(
        Renamed,
        {
            {"SubmissionKey", type text},
            {"CycleKey", type text},
            {"DepartmentCode", type text},
            {"Revision", Int64.Type},
            {"Status", type text},
            {"IsActiveApproval", type logical},
            {"ApprovedFileName", type text},
            {"SubmissionUpdatedUTC", type datetime}
        }
    ),
    Filtered = Table.SelectRows(
        Typed,
        each
            [Status] = "Approved"
            and [IsActiveApproval] = true
            and [ApprovedFileName] <> null
            and [ApprovedFileName] <> ""
    ),
    LatestByDepartment = Table.Group(
        Filtered,
        {"CycleKey", "DepartmentCode"},
        {
            {
                "MaxRevision",
                each List.Max([Revision]),
                Int64.Type
            }
        }
    ),
    Joined = Table.NestedJoin(
        Filtered,
        {"CycleKey", "DepartmentCode", "Revision"},
        LatestByDepartment,
        {"CycleKey", "DepartmentCode", "MaxRevision"},
        "Latest",
        JoinKind.Inner
    ),
    Result = Table.RemoveColumns(Joined, {"Latest"})
in
    Result

Fact Metrics Query

Create a query named FactMetrics. It joins approved file names to active approval records, reads tblMetrics, and applies explicit data types.

let
    Joined = Table.NestedJoin(
        ApprovedFiles,
        {"SourceFileName"},
        ActiveSubmissions,
        {"ApprovedFileName"},
        "Submission",
        JoinKind.Inner
    ),
    ExpandedSubmission = Table.ExpandTableColumn(
        Joined,
        "Submission",
        {
            "SubmissionKey",
            "CycleKey",
            "DepartmentCode",
            "Revision",
            "SubmissionUpdatedUTC"
        },
        {
            "SubmissionKey",
            "CycleKey",
            "DepartmentCode",
            "Revision",
            "SubmissionUpdatedUTC"
        }
    ),
    WithMetricRows = Table.AddColumn(
        ExpandedSubmission,
        "MetricRows",
        each fnGetNamedTable([Content], "tblMetrics"),
        type table
    ),
    ExpandedMetrics = Table.ExpandTableColumn(
        WithMetricRows,
        "MetricRows",
        {
            "MetricCode",
            "ActualValue",
            "PlanValue",
            "ForecastValue",
            "SourceAsOfDate",
            "DataQualityNote",
            "ValidationResult"
        },
        {
            "MetricCode",
            "ActualValue",
            "PlanValue",
            "ForecastValue",
            "SourceAsOfDate",
            "DataQualityNote",
            "ValidationResult"
        }
    ),
    Selected = Table.SelectColumns(
        ExpandedMetrics,
        {
            "SubmissionKey",
            "CycleKey",
            "DepartmentCode",
            "Revision",
            "MetricCode",
            "ActualValue",
            "PlanValue",
            "ForecastValue",
            "SourceAsOfDate",
            "DataQualityNote",
            "ValidationResult",
            "SourceFileName",
            "SourceModifiedUTC",
            "SubmissionUpdatedUTC"
        }
    ),
    Typed = Table.TransformColumnTypes(
        Selected,
        {
            {"SubmissionKey", type text},
            {"CycleKey", type text},
            {"DepartmentCode", type text},
            {"Revision", Int64.Type},
            {"MetricCode", type text},
            {"ActualValue", type number},
            {"PlanValue", type number},
            {"ForecastValue", type number},
            {"SourceAsOfDate", type date},
            {"DataQualityNote", type text},
            {"ValidationResult", type text},
            {"SourceFileName", type text},
            {"SourceModifiedUTC", type datetime},
            {"SubmissionUpdatedUTC", type datetime}
        }
    )
in
    Typed

Fact Commentary Query

Create a query named FactCommentary. It uses the same active-file join and reads only the named commentary table.

let
    Joined = Table.NestedJoin(
        ApprovedFiles,
        {"SourceFileName"},
        ActiveSubmissions,
        {"ApprovedFileName"},
        "Submission",
        JoinKind.Inner
    ),
    ExpandedSubmission = Table.ExpandTableColumn(
        Joined,
        "Submission",
        {
            "SubmissionKey",
            "CycleKey",
            "DepartmentCode",
            "Revision"
        },
        {
            "SubmissionKey",
            "CycleKey",
            "DepartmentCode",
            "Revision"
        }
    ),
    WithCommentaryRows = Table.AddColumn(
        ExpandedSubmission,
        "CommentaryRows",
        each fnGetNamedTable([Content], "tblCommentary"),
        type table
    ),
    ExpandedCommentary = Table.ExpandTableColumn(
        WithCommentaryRows,
        "CommentaryRows",
        {
            "SectionCode",
            "CommentaryText",
            "RiskLevel",
            "ActionOwnerEmail",
            "TargetDate"
        },
        {
            "SectionCode",
            "CommentaryText",
            "RiskLevel",
            "ActionOwnerEmail",
            "TargetDate"
        }
    ),
    Selected = Table.SelectColumns(
        ExpandedCommentary,
        {
            "SubmissionKey",
            "CycleKey",
            "DepartmentCode",
            "Revision",
            "SectionCode",
            "CommentaryText",
            "RiskLevel",
            "ActionOwnerEmail",
            "TargetDate",
            "SourceFileName"
        }
    ),
    Typed = Table.TransformColumnTypes(
        Selected,
        {
            {"SubmissionKey", type text},
            {"CycleKey", type text},
            {"DepartmentCode", type text},
            {"Revision", Int64.Type},
            {"SectionCode", type text},
            {"CommentaryText", type text},
            {"RiskLevel", type text},
            {"ActionOwnerEmail", type text},
            {"TargetDate", type date},
            {"SourceFileName", type text}
        }
    )
in
    Typed

Reporting Cycles Query

Create a query named ReportingCycles. If the SharePoint list uses different internal field names, map them explicitly rather than changing report logic in several locations.

let
    Lists = SharePoint.Tables(pSiteUrl, [ApiVersion = 15]),
    CycleItems = Lists{[Title = "Reporting Cycles"]}[Items],
    Selected = Table.SelectColumns(
        CycleItems,
        {
            "Title",
            "PeriodEnd",
            "CutoffUTC",
            "Status",
            "ExpectedDepartmentCount",
            "IsCurrentPackage",
            "RefreshToken",
            "PackageVersion",
            "Modified"
        }
    ),
    Renamed = Table.RenameColumns(
        Selected,
        {
            {"Title", "CycleKey"},
            {"Modified", "CycleUpdatedUTC"}
        }
    ),
    Typed = Table.TransformColumnTypes(
        Renamed,
        {
            {"CycleKey", type text},
            {"PeriodEnd", type date},
            {"CutoffUTC", type datetimezone},
            {"Status", type text},
            {"ExpectedDepartmentCount", Int64.Type},
            {"IsCurrentPackage", type logical},
            {"RefreshToken", type text},
            {"PackageVersion", Int64.Type},
            {"CycleUpdatedUTC", type datetime}
        }
    )
in
    Typed

Load Audit Query

Create a query named LoadAudit. It produces a single timestamp that changes only when the semantic model refreshes.

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

Set the SharePoint sources to the organization privacy level, authenticate with an authorized organizational account, and publish the semantic model. Configure the Power BI service credentials and run a manual refresh before connecting Power Automate.

Power BI Relationships and Measures

Create one-to-many relationships from ReportingCycles to FactMetrics and FactCommentary using CycleKey. Relate Metric Catalog to FactMetrics using MetricCode. Avoid bidirectional relationships unless a tested reporting requirement needs them.

Actual =
SUM(FactMetrics[ActualValue])

Plan =
SUM(FactMetrics[PlanValue])

Forecast =
SUM(FactMetrics[ForecastValue])

Variance to Plan =
[Actual] - [Plan]

Variance Percentage =
DIVIDE([Variance to Plan], [Plan])

Approved Submission Count =
DISTINCTCOUNT(FactMetrics[SubmissionKey])

Current Package Version =
CALCULATE(
    MAX(ReportingCycles[PackageVersion]),
    ReportingCycles[IsCurrentPackage] = TRUE()
)

Refresh Verification Query

Place the following DAX query in the Power BI action that runs a query against the semantic model. It returns the current cycle and the token that Power Query loaded.

EVALUATE
VAR CurrentCycle =
    TOPN(
        1,
        FILTER(
            ReportingCycles,
            ReportingCycles[IsCurrentPackage] = TRUE()
        ),
        ReportingCycles[PeriodEnd],
        DESC
    )
RETURN
    SELECTCOLUMNS(
        CurrentCycle,
        "CycleKey", ReportingCycles[CycleKey],
        "RefreshToken", ReportingCycles[RefreshToken],
        "ExpectedDepartmentCount", ReportingCycles[ExpectedDepartmentCount],
        "ApprovedSubmissionCount", [Approved Submission Count],
        "LoadedAtUTC", CALCULATE(MAX(LoadAudit[LoadedAtUTC]))
    )

In Power Automate, use a Do Until loop with a bounded timeout and delay between queries. Compare the returned token with the flow variable named RefreshToken. If using the standard query response structure, the comparison expression is:

@equals(
  first(
    first(
      first(body('Run_a_query_against_a_dataset')?['results'])?['tables']
    )?['rows']
  )?['[RefreshToken]'],
  variables('RefreshToken')
)

Action names affect expression references. Rename the action consistently or update the expression. Inspect one successful test run because connector output labels can vary across versions.

Important Power Automate Expressions

Submission key:
concat(
  triggerBody()?['CycleKey'],
  '-',
  toUpper(triggerBody()?['DepartmentCode']),
  '-r',
  formatNumber(int(triggerBody()?['Revision']), '00')
)

Package key:
concat(
  'PKG-',
  variables('CycleKey'),
  '-v',
  formatNumber(int(variables('PackageVersion')), '00')
)

Refresh token:
guid()

Approved file name:
concat(
  'BoardInput_',
  variables('CycleKey'),
  '_',
  variables('DepartmentCode'),
  '_r',
  formatNumber(int(variables('Revision')), '00'),
  '.xlsx'
)

Package file name:
concat(
  'Board_Package_',
  variables('CycleKey'),
  '_v',
  formatNumber(int(variables('PackageVersion')), '00'),
  '.pdf'
)

Approved-submission count:
length(body('Get_active_approved_submissions')?['value'])

Standard approval outcome check:
equals(
  outputs('Start_and_wait_for_an_approval')?['body/outcome'],
  'Approve'
)

Power Automate Scope Configuration

Place the main actions in a scope named Try. Add a Catch scope configured to run after Try has failed, timed out, or been skipped unexpectedly. Add a Finally scope configured to run after both Try and Catch.

The Catch scope should:

1. Set AutomationStatus to Failed or Retry.
2. Increment RetryCount.
3. Write a sanitized error summary to the relevant cycle, submission, or package record.
4. Create an Automation Error Log item containing the flow name, record key, action stage, timestamp, retry count, and run link.
5. Notify the analytics manager and, for authentication or permission failures, the IT owner.

The Finally scope should update LastAutomationRun and release any processing lock. Do not mark a record Complete in Finally unless the Try scope explicitly set a success variable.

Use bounded retry policies for transient SharePoint, Excel, and Power BI actions. Avoid unlimited loops. A typical design uses exponential retry where supported, followed by a manual-review status after the defined maximum.

Paginated Report Configuration

Build the PDF layout in Power BI Report Builder using the published semantic model as its source. Create datasets for summary metrics, charts, commentary, risks, and definitions. Apply a filter requiring ReportingCycles.IsCurrentPackage to equal true.

Publish the paginated report to a workspace that supports the required export operation. In Power Automate, configure the paginated-report export action with:

• The production Power BI workspace
• The published board-package report
• PDF as the output format
• The current cycle or current-package filter where the action supports parameters
• A descriptive file name generated from CycleKey and PackageVersion

The export action returns file content. Pass that content directly to SharePoint’s create-file action. Store the resulting SharePoint identifier, not only the URL.

To test the configuration, run a semantic-model refresh manually, execute the DAX verification query, export the paginated report manually, and compare all metrics with the approved Excel snapshots. Common errors include missing named tables, changed SharePoint internal field names, expired Power BI credentials, unsupported workspace export features, invalid data types, and duplicate active approvals.

Failure Handling and Operational Reliability

Idempotency means the same event can be received more than once without creating duplicate business results. The design achieves this through unique submission and package keys, status-based trigger conditions, processing tokens, explicit file identifiers, and checks before file creation or approval initiation.

The Automation Error Log acts as the manual-review queue. Staff filter it by Open status, severity, flow, and record key. Recovery notes must state the root cause, corrective action, person completing recovery, and whether a retry created a new package version.

A daily reconciliation flow should compare active approved SharePoint submissions with files found in Approved Inputs. A file without a corresponding active record, or an active record without a file, creates a high-priority exception.

A Complete Example

The analytics manager opens cycle BR-2026-Q2 for the period ending June 30, 2026. Five departmental submissions are required, with a cutoff of July 8 at the configured local time converted to UTC.

The finance data owner updates the Finance workbook. One row contains:

MetricCode: DSO_DAYS
ActualValue: 51
PlanValue: 45
ForecastValue: 48
SourceAsOfDate: 2026-06-30
DataQualityNote: blank

The relative variance is greater than the catalog threshold, so the Excel formula displays Review Note Required. The user nevertheless submits revision one.

Power Automate generates BR-2026-Q2-FIN-r01, copies the workbook to staging, and reads the named tables. Its deterministic validation finds that DSO_DAYS exceeds the threshold and has no DataQualityNote. The submission moves to Validation Failed. No approval is created, and the finance data owner receives a link to the record and the specific validation message.

The owner adds an approved explanation describing the invoice-timing issue without naming customers, then submits revision two. Power Automate generates BR-2026-Q2-FIN-r02. Validation succeeds, the finance reviewer confirms the reconciliation, and the department executive approves the content.

The staged file becomes BoardInput_BR-2026-Q2_FIN_r02.xlsx in Approved Inputs. Its returned SharePoint identifier and link are stored in the Submission Register. Revision one remains in Validation Failed status and is not loaded by Power Query.

When all five departments have active approved submissions, the package flow compares the approved count with the expected count of five. It sets BR-2026-Q2 as the current package, increments PackageVersion to one, and writes a new refresh GUID to the cycle.

Power BI refreshes. Power Query joins the five approved file names to the active submission records, loads 32 metric rows and 15 commentary sections, and imports the new refresh token. Power Automate runs the DAX verification query until the returned token matches.

The paginated report renders the current quarter, comparisons, visuals, commentary, risk items, and definitions. Power Automate creates Board_Package_BR-2026-Q2_v01.pdf and a package record named PKG-BR-2026-Q2-v01.

The CFO receives a review link to that exact PDF and approves it. The board liaison confirms the named recipient group and approves release. Power Automate copies the unchanged file to the Released folder, stores the released file identifier, updates the cycle to Released, and sends board recipients a secure link.

If the CFO had rejected the package, version one would have remained intact in Review. The cycle would have moved to Rework, any corrected department input would have required a new approved revision, and the next generated PDF would have been version two.

Implementation Cost

The following amounts are representative planning assumptions, not vendor quotations or verified client costs. Existing Microsoft subscriptions are treated as existing business costs. Organizations must confirm licensing, capacity, storage, external-sharing, and connector requirements for their own tenant.

Estimated Time and Cost Savings

The estimate uses the complete quarterly package as the workflow record. Four packages per year equal an average monthly volume of 0.333 packages.

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

Actual calculation: 0.333 × 4,500 ÷ 60 = 24.98 hours

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

Actual calculation: 0.333 × 1,080 ÷ 60 + 0.333 × 15% × 180 ÷ 60 + 2 = 8.14 hours

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

Actual calculation: 24.98 − 8.14 = 16.84 hours

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

Actual calculation: 16.84 × $65 = approximately $1,095

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

Actual calculation: $1,095 − $180 = approximately $915

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

Actual calculation: $20,840 ÷ $915 = approximately 22.8 months

Recovered time does not automatically reduce payroll. It may provide additional analytical capacity, reduce overtime, improve review quality, shorten reporting turnaround, or allow the company to handle more reporting obligations without adding administrative effort.

Non-financial benefits include clearer ownership, fewer follow-up emails, consistent metric definitions, fewer incomplete submissions, reproducible packages, better approval evidence, stronger version control, and reduced dependency on one analyst.

Organizations should replace the package volume, current effort, future effort, exception rate, loaded labour cost, licensing allowance, implementation hours, and maintenance estimate with their own figures.

Adding AI to the Automation

AI should be added only after the core workflow reliably validates, approves, refreshes, generates, and distributes the package.

Normal automation produces most of the control benefits. Required fields prevent missing values. Lookup tables validate metric codes. Thresholds identify material variances. Approvals determine whether data can enter the model. Permissions control access. None of these tasks require AI.

Potential AI applications include drafting an executive summary from approved inputs, shortening departmental commentary, classifying approved risk descriptions, identifying repeated themes, and suggesting where management explanations appear incomplete.

AI should not calculate authoritative metrics, select the approved version, release the package, infer undisclosed causes, or determine whether a department’s performance is acceptable.

The Recommended AI Enhancement

The recommended enhancement is an AI-generated first draft of board-level commentary using only approved metrics and approved departmental explanations.

• Trigger: All departmental submissions are approved and the semantic model has passed refresh verification
• AI input: Approved metric values, plan and forecast comparisons, approved explanations, section code, and writing constraints
• System instruction: Use only supplied facts, cite supporting metric codes, and return structured JSON
• Expected output: Concise draft commentary, source references, missing-information flags, and confidence level
• Validation: Parse JSON, validate section code, verify cited metric codes, and enforce length limits
• Record update: Store the draft in an AI Commentary Drafts list with Draft status
• Human review: Analytics reviews and edits the draft before it can enter the board package
• Low confidence: Use the approved source commentary without AI rewriting
• Failure behavior: Log the failure and continue with the non-AI workflow

The exact reusable system instruction is:

You draft board-report commentary from approved business inputs.

Use only facts explicitly present in the supplied JSON. Do not invent causes, forecasts, customers, events, risks, actions, or conclusions. Do not calculate a value unless the supplied input explicitly requests that calculation.

Every factual statement must be supported by at least one supplied metric code or approved note. Keep the language neutral and concise. Do not make approval, investment, employment, legal, safety, or accounting decisions.

If the approved inputs do not support a useful explanation, identify the missing information instead of guessing.

Return only valid JSON matching the required schema.

The reusable user prompt is:

Prepare a draft for board-report section {{SECTION_CODE}}.

Approved reporting period: {{CYCLE_KEY}}
Maximum commentary length: 1,200 characters

Approved inputs:
{{APPROVED_INPUT_JSON}}

Return:
1. The section code
2. Draft commentary
3. Each factual claim and its supporting metric codes
4. Missing information
5. Confidence of high, medium, or low

Do not include facts that are not present in the approved inputs.

The expected output schema is:

{
  "type": "object",
  "required": [
    "section_code",
    "draft_commentary",
    "claims",
    "missing_information",
    "confidence"
  ],
  "properties": {
    "section_code": {
      "type": "string"
    },
    "draft_commentary": {
      "type": "string",
      "maxLength": 1200
    },
    "claims": {
      "type": "array",
      "items": {
        "type": "object",
        "required": ["text", "source_metric_codes"],
        "properties": {
          "text": {
            "type": "string"
          },
          "source_metric_codes": {
            "type": "array",
            "items": {
              "type": "string"
            }
          }
        }
      }
    },
    "missing_information": {
      "type": "array",
      "items": {
        "type": "string"
      }
    },
    "confidence": {
      "type": "string",
      "enum": ["high", "medium", "low"]
    }
  }
}

An approved enterprise AI endpoint can be called from Power Automate through a governed custom connector or HTTP action. Credentials must be stored in the connection or an approved secret store. A representative request body is:

{
  "messages": [
    {
      "role": "system",
      "content": "YOUR_APPROVED_SYSTEM_INSTRUCTION"
    },
    {
      "role": "user",
      "content": "YOUR_RENDERED_USER_PROMPT"
    }
  ],
  "temperature": 0.1,
  "response_format": {
    "type": "json_object"
  }
}

The exact endpoint, API version, authentication configuration, and structured-output features depend on the approved AI service and deployed model. Test those capabilities before activation rather than assuming every model supports the same request options.

Power Automate should parse the response, confirm that SectionCode matches the request, verify that every cited metric exists in the supplied input, and reject commentary that exceeds the length limit. Low-confidence, malformed, unsupported, or uncited output must not enter the package.

Prohibited AI inputs include individual payroll data, employee health information, customer credentials, bank information, privileged legal advice, unapproved forecasts, and personal data unnecessary for the board narrative. Prompts and outputs should be logged according to the organization’s privacy and retention policy, with sensitive values minimized.

Benefits of the AI Enhancement

• Less time condensing approved departmental explanations
• More consistent structure across board sections
• Faster preparation of first drafts
• Clearer linkage between narrative claims and metric codes
• Quicker identification of sections lacking a supported explanation
• Improved handling of repetitive, unstructured commentary

These are AI-specific benefits. Controlled intake, approvals, refresh verification, document generation, secure distribution, and audit history are benefits of the core automation and do not depend on AI.

What Remains Rule-Based or Human-Controlled

• Metric calculations: Calculated through approved source systems, Excel formulas, Power Query, or DAX because the same inputs must always produce the same result.
• Materiality thresholds: Stored in the Metric Catalog because management defines them explicitly.
• Approval status: Determined by authenticated human responses, not language-model output.
• Accounting interpretations: Remain with qualified finance personnel.
• Risk acceptance: Remains with accountable executives or the board.
• Employee-related conclusions: Remain under authorized human review because they may affect people and create legal or ethical risk.
• Final commentary: Approved by management because AI may omit context or phrase a supported fact poorly.
• Package release: Authorized by the board liaison or designated executive.
• Recipient access: Controlled through identity, permissions, and approved distribution records.

Estimating the Additional Value of AI

The representative package contains 15 commentary sections. At four packages per year, the monthly average is approximately five sections.

Assume AI saves eight initial minutes per section, 25 percent of drafts need four additional correction minutes, and five percent of requests fail and require the full 14-minute manual process.

Gross AI time saved: 5 sections × 8 minutes ÷ 60 = 0.67 hours per month

Correction time: 5 × 25% × 4 minutes ÷ 60 = 0.08 hours per month

Failure fallback time: 5 × 5% × 14 minutes ÷ 60 = 0.06 hours per month

Net additional capacity: 0.67 − 0.08 − 0.06 = approximately 0.53 hours per month

Labour value: 0.53 × $65 = approximately $34 per month

Net value after the $15 AI allowance: Approximately $19 per month

At this volume, AI may not be justified on labour savings alone. Its stronger value may be consistency, traceability of narrative claims, and faster preparation during a compressed review window. The company should monitor actual usage, correction rates, service failures, and reviewer acceptance before expanding it.

Testing Checklist

Use synthetic sample data and test identities before processing real board information.

Ongoing Maintenance

The analytics manager is the primary system owner. A second trained analytics or finance employee is the backup owner. IT owns identity, SharePoint, Power BI tenant, and connection issues.

Credential rotation must be coordinated with connection testing. Deactivating a former employee is incomplete until that person has also been removed from flow ownership, connection references, SharePoint groups, Power BI workspaces, approval directories, and package distribution groups.

Archive old draft files according to policy, but retain approved snapshots and released packages for the required period. Update documentation whenever fields, metric definitions, thresholds, approvers, report layouts, or flow logic change.

When to Move to Dedicated Software

The implementation remains suitable while quarterly volume is moderate, departmental templates are stable, and Microsoft 365 governance meets the company’s security and audit needs.

Dedicated performance-management, financial-reporting, or board-management software should be evaluated when one or more of the following conditions become material:

• Reporting expands from quarterly packages to frequent multi-entity consolidation
• Metric calculations require complex financial consolidation or currency translation
• Many locations or subsidiaries need delegated administration
• Formal regulatory requirements exceed SharePoint and workflow controls
• Board members require offline mobile access or specialized annotation
• Committee-specific permissions become difficult to manage
• Electronic signatures, attestations, or certified records are required
• Customer-facing or board-facing portals need a purpose-built experience
• Exception rates increase because templates cannot represent the process
• Power Query refresh or file volume approaches practical performance limits
• Integration requirements expand to many operational and financial systems
• Maintenance effort becomes disproportionate to reporting value
• Vendor support and service-level commitments become mandatory
• Security risk increases because external sharing is difficult to govern

Higher volume alone does not require immediate replacement. The organization should first determine whether indexing, dataflows, a governed database, improved APIs, or a revised model can extend the implementation safely.

Implementation Checklist

• Confirm reporting objectives, cycles, owners, cutoff dates, and approval authority
• Confirm Excel, SharePoint, Power Query, Power BI, Power Automate, Teams, and Outlook responsibilities
• Verify required tenant features, licenses, capacity, and external-access policy
• Create development, testing, and production resources
• Assign primary and backup system owners
• Configure least-privilege roles and test identities
• Create Reporting Cycles, Submission Register, catalogs, package, distribution, and error lists
• Create draft, staging, approved, review, released, withdrawn, and archive libraries
• Build protected Excel intake templates with named tables
• Define unique cycle, submission, package, and file naming conventions
• Configure required fields, dropdowns, formulas, and deterministic validation
• Build Power Query extraction from active approved files
• Create the Power BI semantic model, relationships, measures, and refresh audit
• Build the operational dashboard and paginated board report
• Document every source-to-destination field mapping
• Build submission validation and approval automation
• Build cutoff reminders and overdue escalations
• Build refresh-token verification and stale-data prevention
• Build versioned PDF generation and SharePoint file creation
• Build CFO review, release approval, and secure distribution
• Configure processing locks, idempotency, retry limits, and error scopes
• Create manual-review and reconciliation procedures
• Apply retention, privacy, access, credential, and audit controls
• Test normal, rejected, duplicate, overdue, failed, unauthorized, and recovery paths
• Reconcile a complete pilot package against approved source data
• Document deployment, rollback, support, and former-user removal procedures
• Replace representative cost and savings assumptions with company figures
• Add AI only after the rule-based workflow is stable
• Require structured AI output, source validation, human review, and fallback behavior
• Schedule permission, connection, report, cost, exception, and documentation reviews
• Define the operational, security, regulatory, and volume thresholds for evaluating dedicated software