How to Create a Reconciliation Report in Power BI with AI

Building a reconciliation report is one of the most fundamental - and often dreaded - tasks in data analysis. Whether you're matching bank statements to your accounting ledger or comparing ad spend to your finance records, the process can feel tedious. This guide will walk you through creating a dynamic and reusable reconciliation report in Power BI, complete with AI-powered features to make finding discrepancies faster.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

What Exactly is a Reconciliation Report?

At its core, a reconciliation report compares two different sets of data to ensure they match. The goal is to identify and explain any differences between them. It’s like double-checking your work to make sure all the numbers line up.

Some common examples include:

• Comparing your company’s cash book transactions against your official bank statements.
• Matching sales logged in your CRM (like Salesforce) with actual payments processed by a gateway (like Stripe).
• Reconciling campaign-level ad spend reported by platforms (like Facebook Ads) with the invoices you receive from them.

Doing this manually in a spreadsheet is possible, but it’s time-consuming, prone to errors, and has to be redone every single time. Using a tool like Power BI automates the heavy lifting and gives you a visual dashboard to quickly spot and investigate issues.

Setting Up Your Power BI Report for Reconciliation

For this walkthrough, let's use a simple, relatable scenario: reconciling a "Sales Ledger" (an export from our internal system) with "Bank Deposits" (a report from our bank). Our goal is to make sure every sale recorded internally matches a deposit in the bank account.

Step 1: Get Your Data into Power BI

First things first, you need to import your two datasets. Power BI can connect to hundreds of data sources, from simple Excel files to complex cloud databases.

1. Open Power BI Desktop. From the Home ribbon, click on Get Data.
2. For our example, we’ll assume both reports are in Excel workbooks. Select Excel workbook and connect to your first file (e.g., Sales Ledger.xlsx).
3. Select the correct sheet from the Navigator window and click Transform Data. This opens the Power Query Editor, which is where the real magic begins.
4. Repeat the process to bring in your second file (e.g., Bank Deposits.xlsx), making sure it also opens in the Power Query Editor.

You should now see both queries (datasets) listed in the Queries pane on the left side of the Power Query window.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 2: Clean and Transform Your Data with Power Query

Your raw data is rarely perfect. Before you can compare your datasets, you need to ensure they are clean and standardized. This is the most important step for an accurate reconciliation.

Here are some essential transformations to perform on both datasets:

• Remove Unnecessary Columns: Keep only the columns you need for reconciliation, such as a transaction ID, date, and amount. Select the columns you don't need, right-click, and select Remove Columns.
• Check Data Types: Power Query often guesses data types, but you should verify them. Make sure date columns are an actual "Date" type, and amount columns are a "Decimal Number" or "Fixed decimal number" type. You can change this by clicking the icon in the column header.
• Standardize Text: If you're matching based on text fields (like customer names or descriptions), ensure the formatting is consistent. Right-click the column header, navigate to Transform, and use options like Clean, Trim, and Lowercase to remove extra spaces and standardize capitalization.

Creating a Unique Key for Matching

To match transactions accurately, you need a unique transaction ID. Sometimes, this already exists. Other times, you may need to create a composite key by combining a few columns (e.g., merging an Order ID and a Date). If you have a clear, unique identifier in both tables (like Transaction_ID), you can move on to the next step.

Step 3: Combine and Compare the Datasets

This is where we combine the two datasets to see what lines up. In Power Query, the easiest way to do this is with a Merge Queries operation, which behaves like a SQL join.

Here’s how to set it up for reconciliation:

1. In the Power Query Editor, while selecting your "Sales Ledger" query, go to the Home ribbon and click Merge Queries.
2. In the top dropdown of the Merge window, "Sales Ledger" should already be selected. In the second dropdown, select your "Bank Deposits" query.
3. Next, select the column(s) that serve as the unique identifier in both tables. For our example, let's click on the "Transaction_ID" column in both the "Sales Ledger" table and the "Bank Deposits" table.
4. This is the crucial part: for the Join Kind, select Full Outer (all rows from both). This type of join keeps everything - all the matched rows, all the rows from the sales ledger that don't have a match in the bank data, and all the rows from the bank data that don't have a match in the sales ledger.
5. Click OK.

You’ll now have a new column at the end of your "Sales Ledger" table named "Bank Deposits." Click the expand icon in the header of this new column. Deselect "Use original column name as prefix" and select only the columns you want to bring in, like "Amount." Click OK.

You now have a single, combined table. Rows with data in both "Sales Ledger Amount" and "Bank Deposit Amount" columns are your matches. Rows where one is filled and the other is null are your discrepancies.

Finally, click Close & Apply on the Home ribbon to load your prepared data into Power BI as a proper report.

Building Your Reconciliation Dashboard in Power BI

With a clean, merged dataset, creating the report visuals is the fun part. The goal is to make the results easy to understand at a glance.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Step 1: Create a "Status" Column with DAX

A calculated column that labels each transaction as "Matched," "Missing in Bank," or "Missing in Ledger" is incredibly helpful. This is done with Data Analysis Expressions (DAX), Power BI's formula language. It's much simpler than it sounds!

1. Go to the Data view (the table icon on the left).
2. Select your merged table. From the Table Tools ribbon, click New Column.
3. Enter the following DAX formula in the formula bar:

Status = 
IF(
    NOT(ISBLANK([Sales Ledger Amount])) && NOT(ISBLANK([Bank Deposit Amount])), 
    "Matched",
    IF(
        ISBLANK([Sales Ledger Amount]), 
        "Missing in Sales Ledger",
        "Missing in Bank"
    )
)

This formula checks if both amount fields have a value. If so, it’s "Matched." If not, it checks which one is blank and labels the discrepancy accordingly.

Step 2: Add Visuals to Your Report

Switch back to the Report view (the bar chart icon on the left) and start building your dashboard.

• KPI Cards: Use Card visuals to display your key summary numbers. Create three cards for:
• Donut Chart for Status Overview: A Donut chart offers a quick visual of your overall reconciliation health. Drag the "Status" column to the Legend field and the "Transaction_ID" (set to "Count") to the Values field.
• The Transaction Details Table: This is your most important visual for digging into the details. Use the Table visual. Add key columns like "Transaction_ID," "Date," "Sales Ledger Amount," "Bank Deposit Amount," and your new "Status" column. Use a filter pane to allow users to quickly filter the table to see only the items marked "Missing in Bank" or "Missing in Sales Ledger".

Power Up Your Report with AI Features

Here’s where you can take your standard reconciliation report to the next level by leveraging Power BI’s built-in AI capabilities. These features help you and your team investigate discrepancies more intuitively.

Ask Questions with the Q&A Visual

Instead of manually slicing and dicing your data with filters, the Q&A visual lets you ask questions in plain English.

Simply add the Q&A visual to your report canvas. Now, your colleagues can type questions directly into it, such as:

• "Show me count of transaction as table by status"
• "What is the total sales ledger amount for status missing in bank?"
• "Which transactions from last week are unmatched?"

This empowers anyone, regardless of their Power BI skills, to investigate the data and find answers quickly, turning your report into an interactive analytical tool.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Automatically Find Outliers with Anomaly Detection

If you're tracking the reconciliation variance over time, anomaly detection can automatically flag unexpected spikes or dips. Let's say you want to see if a certain day had an unusually high number of mismatches.

1. Create a Line chart showing the daily count of mismatched transactions ("Date" on the Axis, and a count of transactions where "Status <> 'Matched'" on the Values).
2. Select the visual, go to the Analytics pane (the magnifying glass icon in the Visualizations pane).
3. Find the Anomaly detection option and click Add.

Power BI will now automatically highlight any data points that fall outside the expected range, helping you instantly spot days with significant reconciliation breakdowns without any manual effort.

Get Instant Summaries with Smart Narratives

The Smart Narrative visual is an AI-powered text box that automatically generates a summary of your key findings in plain language.

Add it to your report canvas, and it will generate text like, "At 250, the count of Matched transactions accounted for 92.59% of all statuses. Missing in Bank had a noticeably lower count at 15." This narrative updates automatically as your data changes or as you apply filters, providing an instant summary for stakeholders who just need the bottom line.

Final Thoughts

Building a reconciliation report in Power BI transforms a tedious manual task into an automated, interactive, and insightful process. By structuring your data correctly in Power Query and using simple DAX, you create a powerful tool for maintaining financial accuracy. Layering on AI features like Q&A and anomaly detection then makes it faster and easier for anyone on your team to find and resolve discrepancies.

While Power BI is an excellent tool, we know that getting started with data modeling and DAX can still present a challenge for busy teams. We created Graphed to remove this friction entirely. You can connect sources like QuickBooks, Stripe, and Shopify in a few clicks, then create a reconciliation dashboard just by asking for it in plain English: "Show me a comparison of QuickBooks sales and Stripe payouts for last month." Our platform automatically generates the live, interactive visuals you need in seconds, freeing you up to focus on the insights, not the setup.