How to Create a Reconciliation Report in Power BI

Ever found yourself staring at two different reports that are supposed to show the same thing but don't? Reconciling data from multiple systems is one of those thankless tasks, like matching up socks after laundry day. You know everything should balance, but a few key pieces are always missing. This tutorial walks you through how to build a dynamic reconciliation report in Power BI, transforming a tedious manual process into an automated, interactive dashboard.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What is a Reconciliation Report?

A reconciliation report is simply a comparison of two sets of data to identify any differences. Think of it like checking your bank statement against your own spending records. You’re making sure that every transaction recorded by the bank matches a transaction you logged. If they don't, you need to investigate the discrepancies.

In business, this happens constantly:

• Comparing sales logged in your CRM (like Salesforce) with actual revenue recorded in your accounting software (like QuickBooks).
• Matching inventory records in your warehouse management system with sales data from your Shopify store.
• Verifying the ad spend reported by Facebook Ads with the invoices from your finance department.

The goal is to catch errors, prevent fraud, and ensure your data is accurate and trustworthy. Manually doing this in a spreadsheet can take hours or even days, but with a tool like Power BI, you can automate most of the work.

Setting Up Your Reconciliation Report: A Step-by-Step Guide

For our example, we'll create a simple financial reconciliation report. Imagine we have two datasets: one is a list of invoices sent from our internal system (Source A), and the other is a list of payments received from our payment processor (Source B). Our goal is to see which invoices have been paid, which haven't, and if there are any payments without a corresponding invoice.

Let's use two simple Excel files for this walkthrough, but the same principles apply if your data is in SQL databases, Salesforce, SharePoint lists, or anywhere else Power BI can connect.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 1: Get Your Data into Power BI

First, you need to connect Power BI to your data sources. In our case, it's two separate files, but you could be connecting to two different tables in a database or two entirely different platforms.

1. Open Power BI Desktop.
2. From the Home ribbon, select Get Data.
3. Since our data is in Excel, choose Excel Workbook. If your data is elsewhere, select the appropriate connector (e.g., SQL Server, Web, etc.).
4. Navigate to your first file (e.g., Invoices.xlsx) and click Open. In the Navigator window, select the worksheet containing your data and click Transform Data. Don't click Load yet! This is a crucial step that many people miss.
5. The Power Query Editor will open. Now, to add the second data source, click on New Source in the Home ribbon, select Excel Workbook again, and connect to your second file (Payments.xlsx). Select the relevant worksheet.

You should now see both of your data sets listed as "Queries" on the left side of the Power Query Editor window.

Step 2: Clean and Standardize Data in Power Query Editor

Data reconciliation is impossible if your data is messy. Power Query Editor is your best friend for cleaning things up before they even enter your data model. This work up front saves massive headaches later.

Check Data Types

Power Query often guesses data types, but it can get them wrong. Scan through each column in both tables and make sure the data types are correct.

• Amount columns should be a number type (like Fixed decimal number for currency).
• Date columns should be the Date or Date/Time type.
• Invoice numbers or transaction IDs should be Text, even if they are made of numbers, to avoid any unintended calculations.

You can change a column's data type by clicking the icon in the column header.

Create a Common Reconciliation Key

To match transactions between the two tables, you need a unique identifier - a "key." This could be an Invoice Number, Transaction ID, or Order ID. If this key exists in both of your tables, great! If not, you might need to create one by combining a few columns.

For example, if you don't have a unique Invoice ID but you do have a customer name, a date, and an amount, you could combine them to create a unique(-ish) identifier. Select the columns you want to combine (holding Ctrl), right-click the header of one of them, and choose Merge Columns. This can act as a lookup key for your reconciliation. In our case, we'll assume both tables have an Invoice ID column.

After you've finished preliminary cleaning and standardization, click Close & Apply in the top-left corner to load the data into your Power BI model.

Step 3: Build the Data Model

Now that your data is loaded, you need to tell Power BI how the two tables relate to each other. We do this in the Model View.

1. Click on the Model view icon on the left-hand side of Power BI.
2. You'll see your two tables represented as boxes.
3. Find the common key in both tables (e.g., Invoice ID).
4. Click and drag the Invoice ID field from one table (e.g., Invoices) and drop it onto the Invoice ID field in the other table (Payments).

Power BI will automatically create a relationship line between them. If you double-click the line, you can see the relationship details. A "many-to-one" relationship (*:1) is common, meaning for every one invoice, there might be many payments (or vice versa).

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 4: Use DAX to Find Matches and Differences

Now for the fun part. We'll use DAX (Data Analysis Expressions) to create calculated columns that do the actual work of reconciliation. Don't feel intimidated, these formulas are more straightforward than they look.

Switch to the Data view and select your "source of truth" table - the primary table you're checking against. Let's use the Invoices table.

Identify Matched Transactions

We want to add a column in our Invoices table to see if a corresponding payment exists in the Payments table. This is a perfect job for the RELATED function, which can "look across" the relationship we just built.

1. With the Invoices table selected, go to the Table tools ribbon and click New column.
2. Enter the following DAX formula:

PaymentAmountReceived = RELATED(Payments[Amount])

This formula creates a new column called PaymentAmountReceived. For each invoice, it finds the matching Invoice ID in the Payments table and pulls in the corresponding payment Amount. If no match is found, it will return a blank.

Calculate the Variance

Now we can easily calculate if there’s a difference between what was invoiced and what was paid.

1. Create another new column in the Invoices table.
2. Use this simple formula:

Variance = Invoices[Amount] - Invoices[PaymentAmountReceived]

This subtracts the amount received from the amount invoiced. If the Variance is zero, the invoice is fully paid. If it's a positive number, it's partially paid or unpaid. If it's blank, it means there was no matching payment at all.

Create a Reconciliation Status Column

To make the report easy to read, let's create a status column that categorizes each invoice as "Fully Paid," "Partially Paid," "Unpaid," or "Overpaid."

1. Create one more new column in the Invoices table.
2. Use a SWITCH formula, which is like a more powerful version of an IF statement.

Reconciliation Status = SWITCH( TRUE(), ISBLANK([PaymentAmountReceived]), "Unpaid", [Variance] = 0, "Fully Paid", [Variance] > 0, "Partially Paid", [Variance] < 0, "Overpaid", "Error" )

This formula checks each condition top-to-bottom and assigns the appropriate status. Now you have a clean, categorized field you can use for visualizations.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 5: Visualize Your Reconciliation Findings

Switch over to the Report view to build the actual dashboard.

Create a Detailed Table or Matrix

The best visual for an itemized reconciliation is a Table or Matrix. Drag one onto your report canvas.

• Drag fields like Invoice ID, Date, Amount, PaymentAmountReceived, Variance, and Reconciliation Status into the visual.
• Use conditional formatting to highlight variances. For the Variance column, you can go to the visualizations pane > Format your visual > Cell elements, turn on Data bars or Background color so that non-zero amounts stand out immediately.

Add Summary Cards

It's helpful to see top-level numbers at a glance. Use the Card visual for this.

• Total Unpaid Amount: Create a card and add a measure to sum the variance for only unpaid items:

Total Unpaid = CALCULATE( SUM(Invoices[Variance]), Invoices[Reconciliation Status] = "Unpaid" || Invoices[Reconciliation Status] = "Partially Paid" )

• Number of Mismatches: Create a card showing a count of everything that isn't "Fully Paid."
• Total Invoiced vs. Total Paid: Two separate cards showing the sum of Invoices[Amount] and Payments[Amount].

Make it Interactive with Slicers

Add Slicers to let users filter the report. A slicer for Reconciliation Status is incredibly useful. Drag that field onto a Slicer visual. Now, users can click to see only 'Unpaid' items or 'Partially Paid' items. A date slicer is also a great addition.

Final Thoughts

Building a reconciliation report in Power BI follows a clear path: connect and shape your data with Power Query, define relationships in the model, add business logic with DAX, and then tell the story with visualizations. While it requires some setup, the payoff is a reliable, automated report that frees you from hours of manual spreadsheet work.

Often, the hardest part of any analysis is simply getting all of your data connected and speaking the same language. This is especially true for marketing and sales teams whose key metrics live in dozens of different places - from Google Analytics and Facebook Ads to Salesforce and Shopify. We created Graphed to solve this exact problem. With one-click integrations, you can connect your data sources in seconds and use simple, conversational language to build dashboards, so you can focus on insights instead of hours of data cleansing and DAX formulas.