ERP Migration Data Integrity Tracker

Build a Power BI reconciliation dashboard to detect ERP migration errors.

Introduction

30 Second Summary

When a company switches from one accounting system to another, billions of dollars in transactions need to land in exactly the right place. One missed entry or mismatched account code can throw off financial reporting for an entire quarter.

In this project, you will build an automated ERP migration reconciliation tracker that detects and classifies data discrepancies between a legacy system and a new ERP, then visualizes the results in a Power BI dashboard.

What You'll Build

A complete data integrity analytics pipeline that joins two general ledger datasets, identifies mismatches, classifies their root causes using Power Query, and presents findings in an interactive reconciliation dashboard.

By the end of this project, you'll have:

  • Two realistic mock general ledger datasets (Legacy ERP and New ERP) with deliberate discrepancies representing real migration failure modes.
  • A working Power BI reconciliation dashboard that automatically surfaces and classifies variances into Mapping Error, Timing Difference, or Data Loss.
  • A portfolio-ready GitHub repository with documentation framing this as an enterprise data integrity engagement.
  • Secret Mission: Add a Resolution Status drill-through tracker giving stakeholders full visibility into issue resolution.

Are there any prerequisites?

You should have basic familiarity with Excel formulas (VLOOKUP/XLOOKUP, SUMIFS) and an understanding of debits and credits in accounting. No prior Power BI experience is required.

Set Up Your Environment

Before you can build an automated reconciliation tracker, you need the right tools in place.

In this step, you will install Power BI Desktop for dashboarding, verify that your Excel installation includes Power Query for data transformation, and create the folder structure that will keep your project files organized.

In this step, get ready to:
  • Install Power BI Desktop.
  • Verify that Excel has Power Query available.
  • Create the project folder structure.
Install Power BI Desktop

Power BI Desktop is the free data visualization tool you will use to build your reconciliation dashboard.

What is Power BI Desktop?

Power BI Desktop is a free Windows application from Microsoft for building interactive data reports and dashboards.

It connects to data sources, lets you transform and model data, and produces visuals you can share. It requires a 64-bit Windows 10 or later machine.

Windows required

Power BI Desktop is only available on Windows. If you are on macOS, you will need access to a Windows machine or a Windows virtual machine to complete this project.

  • Press the Windows key to open your search bar.
  • Type Power BI Desktop and press Enter.

✔️ Power BI Desktop opens and I see the start screen

Power BI Desktop is ready to go. You can close it for now.

ⓧ Nothing opens or I don't have it installed

You need to install Power BI Desktop. The easiest method is through the Microsoft Store, which provides automatic updates.

  • Press the Windows key, type Microsoft Store, and press Enter.
  • In the Microsoft Store search bar, type Power BI Desktop and press Enter.
  • Find the app published by Microsoft Corporation and click Get.
  • Wait for the download and installation to complete.
  • Once installed, click Open in the Microsoft Store or search for Power BI Desktop from the Start menu.
  • Confirm you see the Power BI Desktop start screen.

Alternative download method

If the Microsoft Store is unavailable, you can download the installer directly from the Microsoft Download Center. Choose the 64-bit version.

Power BI Desktop won't launch?

Make sure your Windows version is Windows 10 or later. Power BI Desktop requires a 64-bit operating system.

If you see a .NET Framework error, update to .NET 4.7.2 or later through Windows Update.

Help me troubleshoot my Power BI Desktop installation on Windows.

Verify Excel has Power Query

Power Query is the data transformation engine you will use to import, clean, and reconcile the GL datasets. It is built into Microsoft 365 and Excel 2019 and later.

  • Press the Windows key to open your search bar.
  • Type Excel and press Enter to open it.
  • Open a new blank workbook.
  • Click the Data tab in the ribbon at the top.
  • Look for the Get & Transform Data group on the left side of the ribbon.

✔️ I see Get & Transform Data

Power Query is available and ready. You can close this workbook without saving.

ⓧ I don't see Get & Transform Data

Your version of Excel may be too old. Power Query is built into Excel 2019 and all Microsoft 365 versions.

  • Check your Excel version by clicking File > Account.
  • If you are on Excel 2016 or earlier, you will need to upgrade to Microsoft 365 or Excel 2019 or later to continue.
  • If you have Microsoft 365 but do not see the options, run Windows Update to ensure your Office installation is current.

Help me find Power Query in my version of Excel.

Create the project folder structure

A clear folder structure keeps your data files, Excel workbooks, Power BI files, and documentation separate and easy to find.

  • Press the Windows key, type PowerShell, and press Enter to open it.
  • Navigate to your Documents folder by running this command:
cd ~/Documents
  • Create the project folder and move into it by running these commands:
mkdir ERP-Migration-Reconciliation
cd ERP-Migration-Reconciliation
  • Create the four subfolders by running this command:
mkdir data, excel, powerbi, docs

What are these folders for?

  • data stores your CSV datasets (Legacy GL, New ERP GL, and the account mapping table).
  • excel stores your Excel workbook with the Power Query reconciliation logic.
  • powerbi stores your Power BI .pbix dashboard file.
  • docs stores your README and any documentation for the portfolio.
  • Confirm the folders exist by running:
ls

You should see four items listed: data, docs, excel, and powerbi.

Don't see all four folders?

Check that you ran the mkdir command with commas separating each folder name. Missing commas will create a single folder with spaces in its name.

If you accidentally created the wrong folder, remove it with Remove-Item and try the mkdir command again.

Help me fix my folder structure in PowerShell.

Your environment is ready. Next up, you will build the first mock dataset that represents a company's Legacy ERP general ledger.

Generate the Legacy ERP Dataset

Your environment is ready. Now it is time to create the first dataset that forms the foundation of your reconciliation project.

In a real ERP migration, the finance team extracts a full General Ledger (GL) from the legacy system before cutover. This extract becomes the source of truth that every transaction in the new system must reconcile against. You will build a realistic mock GL dataset representing a company's January 2026 transactions.

In this step, get ready to:
  • Create a Legacy GL dataset with 60 transactions spanning 8 account codes and 4 departments.
  • Verify the dataset is balanced (total debits equal total credits).
  • Document the Legacy Chart of Accounts in a reference tab.
Create the Legacy GL dataset in Excel

A General Ledger records every financial transaction a company makes. Each entry has a transaction ID, an account code classifying what type of money moved, a date, and debit/credit amounts that must always balance.

  • Switch back to Excel (already open from the previous step).
  • In the blank workbook, rename the first sheet tab to Legacy_GL by double-clicking the tab name at the bottom.
  • In row 1, enter the following 9 column headers starting in cell A1:
TransactionID  AccountCode  AccountName  TransactionDate  Debit  Credit  DepartmentID  DepartmentName  Description

What do these columns represent?

  • TransactionID uniquely identifies each journal entry.
  • AccountCode and AccountName classify the transaction by its place in the Chart of Accounts.
  • Debit and Credit record dollar amounts. In double-entry accounting, every debit must have a matching credit somewhere.
  • DepartmentID and DepartmentName track which business unit owns the transaction.

Now populate the dataset. The Legacy GL contains 60 transactions for January 2026, spread across 8 account codes and 4 departments.

  • Enter the following data starting in row 2 (one transaction per row):
TXN-001,1010,Cash,2026-01-02,15000.00,0.00,FIN,Finance,Opening cash receipt from client Alpha
TXN-002,4010,Revenue,2026-01-02,0.00,15000.00,SAL,Sales,Revenue recognized - Client Alpha
TXN-003,5010,COGS,2026-01-03,8500.00,0.00,OPS,Operations,Cost of goods - January batch
TXN-004,2010,Accounts Payable,2026-01-03,0.00,8500.00,OPS,Operations,Supplier invoice - raw materials
TXN-005,6010,Salaries,2026-01-05,22000.00,0.00,HR,Human Resources,January payroll - week 1
TXN-006,1010,Cash,2026-01-05,0.00,22000.00,HR,Human Resources,Payroll disbursement - week 1
TXN-007,1200,Accounts Receivable,2026-01-07,12500.00,0.00,SAL,Sales,Invoice #1042 - Client Beta
TXN-008,4010,Revenue,2026-01-07,0.00,12500.00,SAL,Sales,Revenue recognized - Client Beta
TXN-009,6020,Rent,2026-01-10,4500.00,0.00,FIN,Finance,Office lease - January
TXN-010,1010,Cash,2026-01-10,0.00,4500.00,FIN,Finance,Rent payment disbursed
TXN-011,1010,Cash,2026-01-12,9800.00,0.00,FIN,Finance,Client Gamma payment received
TXN-012,1200,Accounts Receivable,2026-01-12,0.00,9800.00,SAL,Sales,Client Gamma - AR cleared
TXN-013,5010,COGS,2026-01-14,6200.00,0.00,OPS,Operations,Cost of goods - mid-month production
TXN-014,2010,Accounts Payable,2026-01-14,0.00,6200.00,OPS,Operations,Supplier invoice - packaging
TXN-015,6010,Salaries,2026-01-15,22000.00,0.00,HR,Human Resources,January payroll - week 2
TXN-016,1010,Cash,2026-01-15,0.00,22000.00,HR,Human Resources,Payroll disbursement - week 2
TXN-017,1200,Accounts Receivable,2026-01-17,18750.00,0.00,SAL,Sales,Invoice #1043 - Client Delta
TXN-018,4010,Revenue,2026-01-17,0.00,18750.00,SAL,Sales,Revenue recognized - Client Delta
TXN-019,2010,Accounts Payable,2026-01-18,5000.00,0.00,OPS,Operations,Payment to supplier - outstanding balance
TXN-020,1010,Cash,2026-01-18,0.00,5000.00,FIN,Finance,Supplier payment disbursed
TXN-021,1010,Cash,2026-01-20,7200.00,0.00,FIN,Finance,Client Epsilon payment received
TXN-022,1200,Accounts Receivable,2026-01-20,0.00,7200.00,SAL,Sales,Client Epsilon - AR cleared
TXN-023,5010,COGS,2026-01-21,4800.00,0.00,OPS,Operations,Cost of goods - specialty order
TXN-024,2010,Accounts Payable,2026-01-21,0.00,4800.00,OPS,Operations,Supplier invoice - specialty materials
TXN-025,6010,Salaries,2026-01-22,22000.00,0.00,HR,Human Resources,January payroll - week 3
TXN-026,1010,Cash,2026-01-22,0.00,22000.00,HR,Human Resources,Payroll disbursement - week 3
TXN-027,1200,Accounts Receivable,2026-01-23,31000.00,0.00,SAL,Sales,Invoice #1044 - Client Zeta
TXN-028,4010,Revenue,2026-01-23,0.00,31000.00,SAL,Sales,Revenue recognized - Client Zeta
TXN-029,6020,Rent,2026-01-24,1200.00,0.00,FIN,Finance,Warehouse lease - January
TXN-030,1010,Cash,2026-01-24,0.00,1200.00,FIN,Finance,Warehouse rent disbursed
TXN-031,1010,Cash,2026-01-25,14300.00,0.00,FIN,Finance,Client Eta payment received
TXN-032,1200,Accounts Receivable,2026-01-25,0.00,14300.00,SAL,Sales,Client Eta - AR cleared
TXN-033,5010,COGS,2026-01-26,9100.00,0.00,OPS,Operations,Cost of goods - large order fulfillment
TXN-034,2010,Accounts Payable,2026-01-26,0.00,9100.00,OPS,Operations,Supplier invoice - bulk materials
TXN-035,3000,Retained Earnings,2026-01-27,0.00,5000.00,FIN,Finance,Prior period adjustment
TXN-036,4010,Revenue,2026-01-27,0.00,2000.00,SAL,Sales,Late revenue adjustment - Client Alpha
TXN-037,1010,Cash,2026-01-27,7000.00,0.00,FIN,Finance,Miscellaneous receipts
TXN-038,6010,Salaries,2026-01-28,22000.00,0.00,HR,Human Resources,January payroll - week 4
TXN-039,1010,Cash,2026-01-28,0.00,22000.00,HR,Human Resources,Payroll disbursement - week 4
TXN-040,1010,Cash,2026-01-29,5500.00,0.00,FIN,Finance,Client Theta partial payment
TXN-041,1200,Accounts Receivable,2026-01-29,0.00,5500.00,SAL,Sales,Client Theta - partial AR clear
TXN-042,5010,COGS,2026-01-29,3400.00,0.00,OPS,Operations,Cost of goods - end of month
TXN-043,2010,Accounts Payable,2026-01-29,0.00,3400.00,OPS,Operations,Supplier invoice - final January
TXN-044,6020,Rent,2026-01-30,800.00,0.00,FIN,Finance,Storage unit - January
TXN-045,1010,Cash,2026-01-30,0.00,800.00,FIN,Finance,Storage payment disbursed
TXN-046,1010,Cash,2026-01-30,22000.00,0.00,SAL,Sales,Client Iota payment received
TXN-047,1200,Accounts Receivable,2026-01-30,0.00,22000.00,SAL,Sales,Client Iota - AR cleared
TXN-048,2010,Accounts Payable,2026-01-31,8500.00,0.00,OPS,Operations,Payment to supplier - January balance
TXN-049,1010,Cash,2026-01-31,0.00,8500.00,FIN,Finance,Supplier payment end of month
TXN-050,4010,Revenue,2026-01-31,0.00,4200.00,SAL,Sales,Accrued revenue - month end
TXN-051,1200,Accounts Receivable,2026-01-31,4200.00,0.00,SAL,Sales,Accrued AR - month end
TXN-052,5010,COGS,2026-01-31,2800.00,0.00,OPS,Operations,Accrued COGS - month end
TXN-053,2010,Accounts Payable,2026-01-31,0.00,2800.00,OPS,Operations,Accrued AP - month end
TXN-054,6010,Salaries,2026-01-31,3500.00,0.00,HR,Human Resources,Bonus accrual - January
TXN-055,2010,Accounts Payable,2026-01-31,0.00,3500.00,HR,Human Resources,Bonus payable accrual
TXN-056,1010,Cash,2026-01-31,8400.00,0.00,FIN,Finance,Client Kappa final payment
TXN-057,1200,Accounts Receivable,2026-01-31,0.00,8400.00,SAL,Sales,Client Kappa - AR cleared
TXN-058,3000,Retained Earnings,2026-01-31,2000.00,0.00,FIN,Finance,Dividend declaration
TXN-059,1010,Cash,2026-01-31,0.00,2000.00,FIN,Finance,Dividend payment
TXN-060,4010,Revenue,2026-01-31,0.00,950.00,SAL,Sales,Interest income - month end

What does this dataset represent?

This is a full month of financial activity for a mid-sized company. The 60 transactions cover:

  • Cash receipts and disbursements (account 1010).
  • Revenue recognition and accruals (account 4010).
  • Payroll cycles across 4 weeks (account 6010).
  • Supplier invoices and payments (account 2010).
  • Cost of goods sold for production runs (account 5010).

Amounts range from $800 to $31,000 to mirror realistic journal entry sizes.

Entering the data efficiently

You can type each row manually, or copy the comma-separated values above into a text editor and save as a temporary .csv file, then open it in Excel. Either way, make sure each value lands in its own column (A through I).

Data landing in one column?

If all values appear in column A separated by commas, Excel did not parse the CSV properly. Select column A, go to the Data tab, click Text to Columns, choose Delimited, check Comma, and click Finish.

help me split my CSV data into columns in Excel

Verify the dataset is balanced and save as CSV

In double-entry accounting, every debit must have a corresponding credit. If your dataset is unbalanced, it means a transaction was entered incorrectly.

  • Click on an empty cell below your data (such as cell E63) and enter a SUM formula to total all debits:
=SUM(E2:E61)
  • In the cell next to it (F63), enter the corresponding formula for credits:
=SUM(F2:F61)

Why must debits equal credits?

This is the fundamental principle of double-entry bookkeeping. Every dollar that enters one account must leave another. A balanced trial balance proves no transactions were lost or duplicated during data entry.

In a real migration, if the legacy extract is already unbalanced, the team must investigate before cutover. Your balanced dataset establishes a clean starting point for the reconciliation.

  • Confirm both values are equal. The totals should both show $339,600.00.
  • Delete the SUM formulas once verified (they are not part of the final CSV).

Now save the dataset as a CSV file in your project folder.

  • Click File in the top menu bar.
  • Click Save As.
  • Click Browse and navigate to your Documents/ERP-Migration-Reconciliation/data folder.
  • In the File name box, type Legacy_GL.
  • Click the Save as type dropdown and select CSV (Comma delimited) (*.csv).
  • Click Save.
  • Excel will warn that only the active sheet will be saved. Click OK.
  • If a second dialog appears about features not supported by CSV format, click Yes.
  • Open File Explorer (Windows) or Finder (macOS) and navigate to Documents/ERP-Migration-Reconciliation/data. You should see Legacy_GL.csv listed.

File saved to the wrong location?

If you cannot find Legacy_GL.csv in the data folder, check your Documents folder or Desktop. Excel sometimes defaults to the last-used save location. Move the file into Documents/ERP-Migration-Reconciliation/data manually if needed.

help me find where Excel saved my Legacy_GL.csv file

Document the Chart of Accounts in a reference tab

A Chart of Accounts (COA) is the master list that defines every account code a company uses. Documenting it separately makes it easy to validate account codes during reconciliation.

  • In Excel, close the Legacy_GL.csv file and open a new workbook by clicking File, then New, then Blank workbook.
  • Rename the first sheet tab to COA_Legacy by double-clicking the tab name at the bottom.
  • In row 1, enter 3 column headers: AccountCode in A1, AccountName in B1, and AccountType in C1.
  • Enter the 8 account codes and their details starting in row 2:
1010,Cash,Asset
1200,Accounts Receivable,Asset
2010,Accounts Payable,Liability
3000,Retained Earnings,Equity
4010,Revenue,Revenue
5010,COGS,Expense
6010,Salaries,Expense
6020,Rent,Expense

What are account types?

Every GL account falls into one of five standard categories:

  • Asset (what the company owns): Cash, Accounts Receivable.
  • Liability (what the company owes): Accounts Payable.
  • Equity (owner's stake): Retained Earnings.
  • Revenue (money earned): Revenue.
  • Expense (money spent): COGS, Salaries, Rent.

These categories determine which side of the balance sheet an account appears on. They will be important later when classifying reconciliation variances.

  • Save this workbook as an Excel file (.xlsx) in your Documents/ERP-Migration-Reconciliation/excel folder with the name Legacy_COA.xlsx.
  • Confirm you can see the COA_Legacy tab at the bottom of your workbook with 8 rows of account data plus the header row.

Tab not showing the correct name?

If the sheet tab still says Sheet1, double-click it again and make sure you type COA_Legacy exactly (no spaces). Press Enter to confirm the rename.

help me rename a sheet tab in Excel

✔️ Awesome, I've got everything!

Great. Double check you have Legacy_GL.csv saved in your data folder with 60 rows of transaction data, and the COA_Legacy tab in your Excel workbook with 8 account codes.

ⓧ I'd like to double check the full data

Here is the complete Legacy_GL.csv file for comparison:

TransactionID,AccountCode,AccountName,TransactionDate,Debit,Credit,DepartmentID,DepartmentName,Description
TXN-001,1010,Cash,2026-01-02,15000.00,0.00,FIN,Finance,Opening cash receipt from client Alpha
TXN-002,4010,Revenue,2026-01-02,0.00,15000.00,SAL,Sales,Revenue recognized - Client Alpha
TXN-003,5010,COGS,2026-01-03,8500.00,0.00,OPS,Operations,Cost of goods - January batch
TXN-004,2010,Accounts Payable,2026-01-03,0.00,8500.00,OPS,Operations,Supplier invoice - raw materials
TXN-005,6010,Salaries,2026-01-05,22000.00,0.00,HR,Human Resources,January payroll - week 1
TXN-006,1010,Cash,2026-01-05,0.00,22000.00,HR,Human Resources,Payroll disbursement - week 1
TXN-007,1200,Accounts Receivable,2026-01-07,12500.00,0.00,SAL,Sales,Invoice #1042 - Client Beta
TXN-008,4010,Revenue,2026-01-07,0.00,12500.00,SAL,Sales,Revenue recognized - Client Beta
TXN-009,6020,Rent,2026-01-10,4500.00,0.00,FIN,Finance,Office lease - January
TXN-010,1010,Cash,2026-01-10,0.00,4500.00,FIN,Finance,Rent payment disbursed
TXN-011,1010,Cash,2026-01-12,9800.00,0.00,FIN,Finance,Client Gamma payment received
TXN-012,1200,Accounts Receivable,2026-01-12,0.00,9800.00,SAL,Sales,Client Gamma - AR cleared
TXN-013,5010,COGS,2026-01-14,6200.00,0.00,OPS,Operations,Cost of goods - mid-month production
TXN-014,2010,Accounts Payable,2026-01-14,0.00,6200.00,OPS,Operations,Supplier invoice - packaging
TXN-015,6010,Salaries,2026-01-15,22000.00,0.00,HR,Human Resources,January payroll - week 2
TXN-016,1010,Cash,2026-01-15,0.00,22000.00,HR,Human Resources,Payroll disbursement - week 2
TXN-017,1200,Accounts Receivable,2026-01-17,18750.00,0.00,SAL,Sales,Invoice #1043 - Client Delta
TXN-018,4010,Revenue,2026-01-17,0.00,18750.00,SAL,Sales,Revenue recognized - Client Delta
TXN-019,2010,Accounts Payable,2026-01-18,5000.00,0.00,OPS,Operations,Payment to supplier - outstanding balance
TXN-020,1010,Cash,2026-01-18,0.00,5000.00,FIN,Finance,Supplier payment disbursed
TXN-021,1010,Cash,2026-01-20,7200.00,0.00,FIN,Finance,Client Epsilon payment received
TXN-022,1200,Accounts Receivable,2026-01-20,0.00,7200.00,SAL,Sales,Client Epsilon - AR cleared
TXN-023,5010,COGS,2026-01-21,4800.00,0.00,OPS,Operations,Cost of goods - specialty order
TXN-024,2010,Accounts Payable,2026-01-21,0.00,4800.00,OPS,Operations,Supplier invoice - specialty materials
TXN-025,6010,Salaries,2026-01-22,22000.00,0.00,HR,Human Resources,January payroll - week 3
TXN-026,1010,Cash,2026-01-22,0.00,22000.00,HR,Human Resources,Payroll disbursement - week 3
TXN-027,1200,Accounts Receivable,2026-01-23,31000.00,0.00,SAL,Sales,Invoice #1044 - Client Zeta
TXN-028,4010,Revenue,2026-01-23,0.00,31000.00,SAL,Sales,Revenue recognized - Client Zeta
TXN-029,6020,Rent,2026-01-24,1200.00,0.00,FIN,Finance,Warehouse lease - January
TXN-030,1010,Cash,2026-01-24,0.00,1200.00,FIN,Finance,Warehouse rent disbursed
TXN-031,1010,Cash,2026-01-25,14300.00,0.00,FIN,Finance,Client Eta payment received
TXN-032,1200,Accounts Receivable,2026-01-25,0.00,14300.00,SAL,Sales,Client Eta - AR cleared
TXN-033,5010,COGS,2026-01-26,9100.00,0.00,OPS,Operations,Cost of goods - large order fulfillment
TXN-034,2010,Accounts Payable,2026-01-26,0.00,9100.00,OPS,Operations,Supplier invoice - bulk materials
TXN-035,3000,Retained Earnings,2026-01-27,0.00,5000.00,FIN,Finance,Prior period adjustment
TXN-036,4010,Revenue,2026-01-27,0.00,2000.00,SAL,Sales,Late revenue adjustment - Client Alpha
TXN-037,1010,Cash,2026-01-27,7000.00,0.00,FIN,Finance,Miscellaneous receipts
TXN-038,6010,Salaries,2026-01-28,22000.00,0.00,HR,Human Resources,January payroll - week 4
TXN-039,1010,Cash,2026-01-28,0.00,22000.00,HR,Human Resources,Payroll disbursement - week 4
TXN-040,1010,Cash,2026-01-29,5500.00,0.00,FIN,Finance,Client Theta partial payment
TXN-041,1200,Accounts Receivable,2026-01-29,0.00,5500.00,SAL,Sales,Client Theta - partial AR clear
TXN-042,5010,COGS,2026-01-29,3400.00,0.00,OPS,Operations,Cost of goods - end of month
TXN-043,2010,Accounts Payable,2026-01-29,0.00,3400.00,OPS,Operations,Supplier invoice - final January
TXN-044,6020,Rent,2026-01-30,800.00,0.00,FIN,Finance,Storage unit - January
TXN-045,1010,Cash,2026-01-30,0.00,800.00,FIN,Finance,Storage payment disbursed
TXN-046,1010,Cash,2026-01-30,22000.00,0.00,SAL,Sales,Client Iota payment received
TXN-047,1200,Accounts Receivable,2026-01-30,0.00,22000.00,SAL,Sales,Client Iota - AR cleared
TXN-048,2010,Accounts Payable,2026-01-31,8500.00,0.00,OPS,Operations,Payment to supplier - January balance
TXN-049,1010,Cash,2026-01-31,0.00,8500.00,FIN,Finance,Supplier payment end of month
TXN-050,4010,Revenue,2026-01-31,0.00,4200.00,SAL,Sales,Accrued revenue - month end
TXN-051,1200,Accounts Receivable,2026-01-31,4200.00,0.00,SAL,Sales,Accrued AR - month end
TXN-052,5010,COGS,2026-01-31,2800.00,0.00,OPS,Operations,Accrued COGS - month end
TXN-053,2010,Accounts Payable,2026-01-31,0.00,2800.00,OPS,Operations,Accrued AP - month end
TXN-054,6010,Salaries,2026-01-31,3500.00,0.00,HR,Human Resources,Bonus accrual - January
TXN-055,2010,Accounts Payable,2026-01-31,0.00,3500.00,HR,Human Resources,Bonus payable accrual
TXN-056,1010,Cash,2026-01-31,8400.00,0.00,FIN,Finance,Client Kappa final payment
TXN-057,1200,Accounts Receivable,2026-01-31,0.00,8400.00,SAL,Sales,Client Kappa - AR cleared
TXN-058,3000,Retained Earnings,2026-01-31,2000.00,0.00,FIN,Finance,Dividend declaration
TXN-059,1010,Cash,2026-01-31,0.00,2000.00,FIN,Finance,Dividend payment
TXN-060,4010,Revenue,2026-01-31,0.00,950.00,SAL,Sales,Interest income - month end

And here is the COA_Legacy tab content:

AccountCode,AccountName,AccountType
1010,Cash,Asset
1200,Accounts Receivable,Asset
2010,Accounts Payable,Liability
3000,Retained Earnings,Equity
4010,Revenue,Revenue
5010,COGS,Expense
6010,Salaries,Expense
6020,Rent,Expense

Your Legacy GL dataset is locked in. Next up, you will create the New ERP dataset with deliberate mismatches that simulate real-world migration failures.

Generate the New ERP Dataset with Deliberate Mismatches

Your Legacy GL dataset is ready with 60 balanced transactions. Now comes the interesting part: building the New ERP dataset that simulates what actually happens during a migration.

Real ERP migrations almost never produce a perfect data copy. Account codes get renumbered, transactions land in wrong periods, and some records vanish entirely. You will deliberately engineer these failure modes so your reconciliation dashboard has realistic discrepancies to detect.

In this step, get ready to:
  • Create the New ERP dataset with three categories of deliberate discrepancies.
  • Introduce rounding differences that simulate data precision issues.
  • Build an Account Mapping reference table for the reconciliation logic.
Create the New ERP dataset with deliberate discrepancies

In a real migration, the new system adopts a different Chart of Accounts numbering scheme. Most transactions migrate correctly under the new codes, but a few fail in predictable ways.

You will embed four types of discrepancies into this dataset. Your reconciliation model will need to detect and classify every single one.

  • Open a new Excel workbook.
  • Copy the following data into the first sheet, starting at cell A1:
TransactionID,AccountCode,AccountName,TransactionDate,Debit,Credit,DepartmentID,DepartmentName,Description
TXN-001,110100,Cash,2026-01-02,15000.00,0.00,FIN,Finance,Opening cash receipt from client Alpha
TXN-002,401000,Revenue,2026-01-02,0.00,15000.00,SAL,Sales,Revenue recognized - Client Alpha
TXN-003,501000,COGS,2026-01-03,8500.00,0.00,OPS,Operations,Cost of goods - January batch
TXN-004,201000,Accounts Payable,2026-01-03,0.00,8500.00,OPS,Operations,Supplier invoice - raw materials
TXN-005,601000,Salaries,2026-01-05,22000.00,0.00,HR,Human Resources,January payroll - week 1
TXN-006,110100,Cash,2026-01-05,0.00,22000.00,HR,Human Resources,Payroll disbursement - week 1
TXN-007,120000,Accounts Receivable,2026-01-07,12499.95,0.00,SAL,Sales,Invoice #1042 - Client Beta
TXN-008,401000,Revenue,2026-01-07,0.00,12499.95,SAL,Sales,Revenue recognized - Client Beta
TXN-009,602000,Rent,2026-01-10,4500.00,0.00,FIN,Finance,Office lease - January
TXN-010,110100,Cash,2026-01-10,0.00,4500.00,FIN,Finance,Rent payment disbursed
TXN-011,110100,Cash,2026-01-12,9800.00,0.00,FIN,Finance,Client Gamma payment received
TXN-012,120000,Accounts Receivable,2026-01-12,0.00,9800.00,SAL,Sales,Client Gamma - AR cleared
TXN-013,501000,COGS,2026-01-14,6200.00,0.00,OPS,Operations,Cost of goods - mid-month production
TXN-014,201000,Accounts Payable,2026-01-14,0.00,6200.00,OPS,Operations,Supplier invoice - packaging
TXN-015,601000,Salaries,2026-01-15,22000.00,0.00,HR,Human Resources,January payroll - week 2
TXN-016,110100,Cash,2026-01-15,0.00,22000.00,HR,Human Resources,Payroll disbursement - week 2
TXN-017,120000,Accounts Receivable,2026-01-17,18750.00,0.00,SAL,Sales,Invoice #1043 - Client Delta
TXN-018,401000,Revenue,2026-01-17,0.00,18750.00,SAL,Sales,Revenue recognized - Client Delta
TXN-019,201000,Accounts Payable,2026-01-18,5000.00,0.00,OPS,Operations,Payment to supplier - outstanding balance
TXN-020,110100,Cash,2026-01-18,0.00,5000.00,FIN,Finance,Supplier payment disbursed
TXN-021,110100,Cash,2026-01-20,7200.00,0.00,FIN,Finance,Client Epsilon payment received
TXN-022,120000,Accounts Receivable,2026-01-20,0.00,7200.00,SAL,Sales,Client Epsilon - AR cleared
TXN-023,501000,COGS,2026-01-21,4800.00,0.00,OPS,Operations,Cost of goods - specialty order
TXN-024,201000,Accounts Payable,2026-01-21,0.00,4800.00,OPS,Operations,Supplier invoice - specialty materials
TXN-025,601000,Salaries,2026-01-22,22000.00,0.00,HR,Human Resources,January payroll - week 3
TXN-026,110100,Cash,2026-01-22,0.00,22000.00,HR,Human Resources,Payroll disbursement - week 3
TXN-027,120000,Accounts Receivable,2026-01-23,31000.00,0.00,SAL,Sales,Invoice #1044 - Client Zeta
TXN-028,401000,Revenue,2026-01-23,0.00,31000.00,SAL,Sales,Revenue recognized - Client Zeta
TXN-029,602000,Rent,2026-01-24,1200.00,0.00,FIN,Finance,Warehouse lease - January
TXN-030,110100,Cash,2026-01-24,0.00,1200.00,FIN,Finance,Warehouse rent disbursed
TXN-031,110100,Cash,2026-01-25,14300.00,0.00,FIN,Finance,Client Eta payment received
TXN-032,120000,Accounts Receivable,2026-01-25,0.00,14300.00,SAL,Sales,Client Eta - AR cleared
TXN-033,501000,COGS,2026-01-26,9100.00,0.00,OPS,Operations,Cost of goods - large order fulfillment
TXN-034,201000,Accounts Payable,2026-01-26,0.00,9100.00,OPS,Operations,Supplier invoice - bulk materials
TXN-035,300000,Retained Earnings,2026-01-27,0.00,5000.00,FIN,Finance,Prior period adjustment
TXN-036,401000,Revenue,2026-01-27,0.00,2000.00,SAL,Sales,Late revenue adjustment - Client Alpha
TXN-037,110100,Cash,2026-01-27,7000.00,0.00,FIN,Finance,Miscellaneous receipts
TXN-038,601000,Salaries,2026-01-28,22000.00,0.00,HR,Human Resources,January payroll - week 4
TXN-039,110100,Cash,2026-01-28,0.00,22000.00,HR,Human Resources,Payroll disbursement - week 4
TXN-040,110100,Cash,2026-01-29,5500.00,0.00,FIN,Finance,Client Theta partial payment
TXN-041,120000,Accounts Receivable,2026-01-29,0.00,5500.00,SAL,Sales,Client Theta - partial AR clear
TXN-042,501000,COGS,2026-01-29,3400.00,0.00,OPS,Operations,Cost of goods - end of month
TXN-043,201000,Accounts Payable,2026-01-29,0.00,3400.00,OPS,Operations,Supplier invoice - final January
TXN-044,602000,Rent,2026-01-30,800.00,0.00,FIN,Finance,Storage unit - January
TXN-045,110100,Cash,2026-01-30,0.00,800.00,FIN,Finance,Storage payment disbursed
TXN-046,1010,Cash,2026-01-30,22000.00,0.00,SAL,Sales,Client Iota payment received
TXN-047,1200,Accounts Receivable,2026-01-30,0.00,22000.00,SAL,Sales,Client Iota - AR cleared
TXN-048,201000,Accounts Payable,2026-01-31,8500.00,0.00,OPS,Operations,Payment to supplier - January balance
TXN-049,110100,Cash,2026-01-31,0.00,8500.00,FIN,Finance,Supplier payment end of month
TXN-050,401000,Revenue,2026-02-01,0.00,4200.00,SAL,Sales,Accrued revenue - month end
TXN-051,120000,Accounts Receivable,2026-02-01,4200.00,0.00,SAL,Sales,Accrued AR - month end
TXN-052,501000,COGS,2026-02-01,2800.00,0.00,OPS,Operations,Accrued COGS - month end
TXN-053,201000,Accounts Payable,2026-02-01,0.00,2800.00,OPS,Operations,Accrued AP - month end
TXN-054,601000,Salaries,2026-01-31,3500.00,0.00,HR,Human Resources,Bonus accrual - January
TXN-055,201000,Accounts Payable,2026-01-31,0.00,3500.00,HR,Human Resources,Bonus payable accrual
TXN-056,110100,Cash,2026-01-31,8399.90,0.00,FIN,Finance,Client Kappa final payment
TXN-057,120000,Accounts Receivable,2026-01-31,0.00,8399.90,SAL,Sales,Client Kappa - AR cleared

What discrepancies are hiding in this dataset?

This dataset contains four categories of real-world migration failures that your reconciliation model will need to detect:

  • Mapping Errors: TXN-046 and TXN-047 still use old Legacy codes (1010 and 1200) instead of the new mapped codes. This represents incomplete code translation during migration.
  • Timing Differences: TXN-050 through TXN-053 are dated 2026-02-01 in the New ERP, but exist as January 31 entries in the Legacy system. These transactions posted in the wrong period during cutover.
  • Data Loss: TXN-058, TXN-059, and TXN-060 from the Legacy system are completely absent. These transactions were dropped during migration.
  • Rounding: TXN-007/TXN-008 and TXN-056/TXN-057 differ by $0.05 to $0.10 from Legacy values. These represent precision issues during data conversion.

Why only 57 rows?

The Legacy GL has 60 transactions, but this New ERP file only has 57. The 3 missing transactions (TXN-058, TXN-059, TXN-060) represent the Data Loss category. Your reconciliation model will flag these as transactions that exist in the source but disappeared during migration.

Verify rounding differences and save the file

Rounding differences are the subtlest migration failure. A $0.05 discrepancy might seem trivial, but across thousands of transactions it can signal a systemic conversion defect.

  • Confirm that row TXN-007 shows a Debit of 12499.95 (the Legacy system shows 12500.00).
  • Confirm that row TXN-056 shows a Debit of 8399.90 (the Legacy system shows 8400.00).
  • Click File, then Save As.
  • Navigate to your ERP-Migration-Reconciliation/data folder.
  • In the Save as type dropdown, select CSV (Comma delimited) (*.csv).
  • Name the file New_ERP_GL and click Save.

Open your data folder in File Explorer (Windows) or Finder (macOS). You should see both Legacy_GL.csv and New_ERP_GL.csv listed.

Excel warning about CSV format?

Excel may show a dialog saying some features are not compatible with CSV format. Click Yes to keep the CSV format. This warning is normal because CSV files cannot store multiple sheets or formatting.

If your Debit/Credit columns show rounded whole numbers instead of two decimal places, select those columns, right-click, choose Format Cells, and set them to Number with 2 decimal places before re-saving.

help me troubleshoot saving my New ERP GL data as a CSV file in Excel

✔️ Awesome, I've got everything!

Great. Double check that your New_ERP_GL.csv has exactly 57 data rows (plus the header row) and that you can spot the discrepancies at TXN-046, TXN-047, TXN-050 through TXN-053, TXN-007/008, and TXN-056/057.

ⓧ I'd like to double check the full code

Here is the complete New_ERP_GL.csv for comparison:

TransactionID,AccountCode,AccountName,TransactionDate,Debit,Credit,DepartmentID,DepartmentName,Description
TXN-001,110100,Cash,2026-01-02,15000.00,0.00,FIN,Finance,Opening cash receipt from client Alpha
TXN-002,401000,Revenue,2026-01-02,0.00,15000.00,SAL,Sales,Revenue recognized - Client Alpha
TXN-003,501000,COGS,2026-01-03,8500.00,0.00,OPS,Operations,Cost of goods - January batch
TXN-004,201000,Accounts Payable,2026-01-03,0.00,8500.00,OPS,Operations,Supplier invoice - raw materials
TXN-005,601000,Salaries,2026-01-05,22000.00,0.00,HR,Human Resources,January payroll - week 1
TXN-006,110100,Cash,2026-01-05,0.00,22000.00,HR,Human Resources,Payroll disbursement - week 1
TXN-007,120000,Accounts Receivable,2026-01-07,12499.95,0.00,SAL,Sales,Invoice #1042 - Client Beta
TXN-008,401000,Revenue,2026-01-07,0.00,12499.95,SAL,Sales,Revenue recognized - Client Beta
TXN-009,602000,Rent,2026-01-10,4500.00,0.00,FIN,Finance,Office lease - January
TXN-010,110100,Cash,2026-01-10,0.00,4500.00,FIN,Finance,Rent payment disbursed
TXN-011,110100,Cash,2026-01-12,9800.00,0.00,FIN,Finance,Client Gamma payment received
TXN-012,120000,Accounts Receivable,2026-01-12,0.00,9800.00,SAL,Sales,Client Gamma - AR cleared
TXN-013,501000,COGS,2026-01-14,6200.00,0.00,OPS,Operations,Cost of goods - mid-month production
TXN-014,201000,Accounts Payable,2026-01-14,0.00,6200.00,OPS,Operations,Supplier invoice - packaging
TXN-015,601000,Salaries,2026-01-15,22000.00,0.00,HR,Human Resources,January payroll - week 2
TXN-016,110100,Cash,2026-01-15,0.00,22000.00,HR,Human Resources,Payroll disbursement - week 2
TXN-017,120000,Accounts Receivable,2026-01-17,18750.00,0.00,SAL,Sales,Invoice #1043 - Client Delta
TXN-018,401000,Revenue,2026-01-17,0.00,18750.00,SAL,Sales,Revenue recognized - Client Delta
TXN-019,201000,Accounts Payable,2026-01-18,5000.00,0.00,OPS,Operations,Payment to supplier - outstanding balance
TXN-020,110100,Cash,2026-01-18,0.00,5000.00,FIN,Finance,Supplier payment disbursed
TXN-021,110100,Cash,2026-01-20,7200.00,0.00,FIN,Finance,Client Epsilon payment received
TXN-022,120000,Accounts Receivable,2026-01-20,0.00,7200.00,SAL,Sales,Client Epsilon - AR cleared
TXN-023,501000,COGS,2026-01-21,4800.00,0.00,OPS,Operations,Cost of goods - specialty order
TXN-024,201000,Accounts Payable,2026-01-21,0.00,4800.00,OPS,Operations,Supplier invoice - specialty materials
TXN-025,601000,Salaries,2026-01-22,22000.00,0.00,HR,Human Resources,January payroll - week 3
TXN-026,110100,Cash,2026-01-22,0.00,22000.00,HR,Human Resources,Payroll disbursement - week 3
TXN-027,120000,Accounts Receivable,2026-01-23,31000.00,0.00,SAL,Sales,Invoice #1044 - Client Zeta
TXN-028,401000,Revenue,2026-01-23,0.00,31000.00,SAL,Sales,Revenue recognized - Client Zeta
TXN-029,602000,Rent,2026-01-24,1200.00,0.00,FIN,Finance,Warehouse lease - January
TXN-030,110100,Cash,2026-01-24,0.00,1200.00,FIN,Finance,Warehouse rent disbursed
TXN-031,110100,Cash,2026-01-25,14300.00,0.00,FIN,Finance,Client Eta payment received
TXN-032,120000,Accounts Receivable,2026-01-25,0.00,14300.00,SAL,Sales,Client Eta - AR cleared
TXN-033,501000,COGS,2026-01-26,9100.00,0.00,OPS,Operations,Cost of goods - large order fulfillment
TXN-034,201000,Accounts Payable,2026-01-26,0.00,9100.00,OPS,Operations,Supplier invoice - bulk materials
TXN-035,300000,Retained Earnings,2026-01-27,0.00,5000.00,FIN,Finance,Prior period adjustment
TXN-036,401000,Revenue,2026-01-27,0.00,2000.00,SAL,Sales,Late revenue adjustment - Client Alpha
TXN-037,110100,Cash,2026-01-27,7000.00,0.00,FIN,Finance,Miscellaneous receipts
TXN-038,601000,Salaries,2026-01-28,22000.00,0.00,HR,Human Resources,January payroll - week 4
TXN-039,110100,Cash,2026-01-28,0.00,22000.00,HR,Human Resources,Payroll disbursement - week 4
TXN-040,110100,Cash,2026-01-29,5500.00,0.00,FIN,Finance,Client Theta partial payment
TXN-041,120000,Accounts Receivable,2026-01-29,0.00,5500.00,SAL,Sales,Client Theta - partial AR clear
TXN-042,501000,COGS,2026-01-29,3400.00,0.00,OPS,Operations,Cost of goods - end of month
TXN-043,201000,Accounts Payable,2026-01-29,0.00,3400.00,OPS,Operations,Supplier invoice - final January
TXN-044,602000,Rent,2026-01-30,800.00,0.00,FIN,Finance,Storage unit - January
TXN-045,110100,Cash,2026-01-30,0.00,800.00,FIN,Finance,Storage payment disbursed
TXN-046,1010,Cash,2026-01-30,22000.00,0.00,SAL,Sales,Client Iota payment received
TXN-047,1200,Accounts Receivable,2026-01-30,0.00,22000.00,SAL,Sales,Client Iota - AR cleared
TXN-048,201000,Accounts Payable,2026-01-31,8500.00,0.00,OPS,Operations,Payment to supplier - January balance
TXN-049,110100,Cash,2026-01-31,0.00,8500.00,FIN,Finance,Supplier payment end of month
TXN-050,401000,Revenue,2026-02-01,0.00,4200.00,SAL,Sales,Accrued revenue - month end
TXN-051,120000,Accounts Receivable,2026-02-01,4200.00,0.00,SAL,Sales,Accrued AR - month end
TXN-052,501000,COGS,2026-02-01,2800.00,0.00,OPS,Operations,Accrued COGS - month end
TXN-053,201000,Accounts Payable,2026-02-01,0.00,2800.00,OPS,Operations,Accrued AP - month end
TXN-054,601000,Salaries,2026-01-31,3500.00,0.00,HR,Human Resources,Bonus accrual - January
TXN-055,201000,Accounts Payable,2026-01-31,0.00,3500.00,HR,Human Resources,Bonus payable accrual
TXN-056,110100,Cash,2026-01-31,8399.90,0.00,FIN,Finance,Client Kappa final payment
TXN-057,120000,Accounts Receivable,2026-01-31,0.00,8399.90,SAL,Sales,Client Kappa - AR cleared
Create the Account Mapping reference table

The reconciliation logic needs a reference that documents how account codes should translate between the two systems. Without this mapping, every code difference looks like an error.

  • Open a new Excel workbook.
  • Copy the following mapping data into the first sheet, starting at cell A1:
Legacy_AccountCode,Legacy_AccountName,New_AccountCode,New_AccountName,AccountType
1010,Cash,110100,Cash,Asset
1200,Accounts Receivable,120000,Accounts Receivable,Asset
2010,Accounts Payable,201000,Accounts Payable,Liability
3000,Retained Earnings,300000,Retained Earnings,Equity
4010,Revenue,401000,Revenue,Revenue
5010,COGS,501000,COGS,Expense
6010,Salaries,601000,Salaries,Expense
6020,Rent,602000,Rent,Expense

What does this mapping table do?

This table documents the intended translation between Legacy and New account codes. When your reconciliation model merges the two GL datasets, it will use this mapping to determine which code differences are expected (correct translations) versus which are errors (codes that failed to translate).

For example, Legacy code 1010 should become 110100 in the New ERP. If the reconciliation finds a transaction still using 1010 in the New system, it flags it as a Mapping Error.

  • Click File, then Save As.
  • Navigate to your ERP-Migration-Reconciliation/data folder.
  • Set the file type to CSV (Comma delimited) (*.csv).
  • Name the file Account_Mapping and click Save.
  • Open your data folder. You should now see three CSV files: Legacy_GL.csv, New_ERP_GL.csv, and Account_Mapping.csv.

Only see two files in your data folder?

Make sure you saved to the correct path. Check that you navigated into the data subfolder inside ERP-Migration-Reconciliation, not the parent folder or your Documents root.

Also verify the file extension is .csv and not .xlsx. If you see Account_Mapping.xlsx, re-save using the CSV file type.

help me find where my CSV files saved in my ERP Migration Reconciliation project

✔️ Awesome, I've got everything!

Great. Confirm you have all three CSV files in your data folder and you are ready to build the reconciliation model.

ⓧ I'd like to double check the full code

Here is the complete Account_Mapping.csv for comparison:

Legacy_AccountCode,Legacy_AccountName,New_AccountCode,New_AccountName,AccountType
1010,Cash,110100,Cash,Asset
1200,Accounts Receivable,120000,Accounts Receivable,Asset
2010,Accounts Payable,201000,Accounts Payable,Liability
3000,Retained Earnings,300000,Retained Earnings,Equity
4010,Revenue,401000,Revenue,Revenue
5010,COGS,501000,COGS,Expense
6010,Salaries,601000,Salaries,Expense
6020,Rent,602000,Rent,Expense

Your three source datasets are ready. You have a clean Legacy GL, a New ERP GL with hidden discrepancies, and a mapping reference that defines how account codes should translate. Next up, you will connect all three in Power Query and build the automated reconciliation logic that surfaces every mismatch.

Build the Excel Reconciliation Model with Power Query

You now have two GL datasets and a mapping table sitting in your data folder. The next challenge is connecting these datasets so you can compare every Legacy transaction against its New ERP counterpart.

In this step, you will build an automated reconciliation engine in Excel using Power Query. Power Query will join your three tables together, calculate variances, and classify every mismatch by root cause.

In this step, get ready to:
  • Import all three CSV files into Power Query with correct data types.
  • Merge the tables using Left Outer Joins to pair Legacy transactions with their New ERP counterparts.
  • Add custom columns that calculate variances and classify root causes automatically.
Import the CSV files into Power Query

Power Query needs to load each CSV as a separate query before you can join them together. You will also lock in the correct data types so that dates, currency values, and codes behave properly in later formulas.

  • Open a new blank workbook in Excel.
  • Click the Data tab in the ribbon.
  • Click Get Data > From File > From Text/CSV.
  • Navigate to your ERP-Migration-Reconciliation/data folder and select Legacy_GL.csv.
  • In the preview window that appears, click Transform Data to open the Power Query Editor.

Why Transform Data instead of Load?

Clicking Transform Data opens the Power Query Editor where you can set data types and build transformations before loading. If you click Load, the data goes straight to a sheet without cleanup.

  • In the Power Query Editor, set the column data types. Right-click the TransactionDate column header, select Change Type, and choose Date.
  • Right-click Debit, select Change Type, and choose Currency. Repeat for the Credit column.
  • Right-click AccountCode, select Change Type, and choose Text. This prevents Power Query from treating account codes as numbers.
  • Repeat the Text type for TransactionID and DepartmentID.

Now import the other two files into the same Power Query session.

  • In the Power Query Editor, click New Source > File > Text/CSV.
  • Select New_ERP_GL.csv and click OK.
  • Set the same data types on the New_ERP_GL query: TransactionDate to Date, Debit and Credit to Currency, AccountCode and TransactionID to Text.
  • Repeat the import for Account_Mapping.csv. Set Legacy_AccountCode and New_AccountCode to Text.

You should now see three queries listed in the Queries pane on the left: Legacy_GL, New_ERP_GL, and Account_Mapping.

Don't see all three queries?

If a query is missing from the Queries pane, confirm you used New Source inside the Power Query Editor rather than closing and reimporting from the Data tab. Each import inside the editor creates a new query in the same session.

Help me troubleshoot my Power Query imports

Build the reconciliation merge logic

With all three tables loaded, you can now join them step by step. The first merge attaches the expected new account code to each Legacy transaction. The second merge pairs each Legacy transaction with its New ERP counterpart using TransactionID.

  • In the Queries pane, select the Legacy_GL query to make it active.
  • Click the Home tab, then click Merge Queries in the Combine group.
  • In the Merge dialog, select Account_Mapping as the right table.
  • In the Legacy_GL preview, click the AccountCode column to highlight it.
  • In the Account_Mapping preview, click Legacy_AccountCode to match it.
  • Set the Join Kind to Left Outer (all from first, matching from second).
  • Click OK.

What is a Left Outer Join?

A Left Outer Join keeps every row from the left table (Legacy_GL) and attaches matching data from the right table (Account_Mapping). If no match exists, the right-side columns show null. This ensures you never lose a Legacy transaction during the merge.

  • A new column called Account_Mapping appears on the right. Click the expand icon (double-arrow) in that column header.
  • Uncheck all columns except New_AccountCode.
  • Uncheck Use original column name as prefix.
  • Click OK.
  • Right-click the new New_AccountCode column header, select Rename, and type Expected_New_AccountCode.

Now perform the second merge to bring in the New ERP data.

  • With the Legacy_GL query still active, click Home > Merge Queries again.
  • Select New_ERP_GL as the right table.
  • Click TransactionID in both the Legacy_GL preview and the New_ERP_GL preview.
  • Set the Join Kind to Left Outer.
  • Click OK.
  • Click the expand icon on the new New_ERP_GL column.
  • Select these columns to expand: TransactionID, AccountCode, TransactionDate, Debit, Credit.
  • Leave Use original column name as prefix checked this time. This prefixes the columns with New_ERP_GL. to distinguish them from Legacy columns.
  • Click OK.

Why use the prefix for the second merge?

Both Legacy_GL and New_ERP_GL have columns named Debit, Credit, and TransactionDate. Keeping the prefix New_ERP_GL. prevents name collisions and makes your variance formulas unambiguous.

Next, add custom columns to calculate the variances. These formulas use Power Query M language.

  • Click the Add Column tab, then click Custom Column.
  • In the New column name box, type Debit_Variance.
  • In the Custom column formula box, paste the following formula:
if [New_ERP_GL.Debit] = null then [Debit] else [Debit] - [New_ERP_GL.Debit]
  • Click OK.

What does this formula do?

If the New ERP has no matching transaction (null), the entire Legacy debit amount is the variance. Otherwise it calculates the difference between the two systems. This ensures Data Loss transactions show their full dollar impact rather than returning a blank.

  • Click Add Column > Custom Column again.
  • Name it Credit_Variance and paste this formula:
if [New_ERP_GL.Credit] = null then [Credit] else [Credit] - [New_ERP_GL.Credit]
  • Click OK.
  • Add one more Custom Column named Match_Status with this formula:
if [New_ERP_GL.TransactionID] = null then "Unmatched"
else if [Debit_Variance] = 0 and [Credit_Variance] = 0 and [Expected_New_AccountCode] = [New_ERP_GL.AccountCode] and [TransactionDate] = [New_ERP_GL.TransactionDate] then "Matched"
else "Unmatched"
  • Click OK.

What does Match_Status check?

A transaction is only "Matched" when all four conditions pass: the New ERP record exists, the debit and credit match exactly, the account code matches the expected mapped code, and the date is identical. Any deviation flags it as "Unmatched" for further classification.

Seeing an Expression.Error?

The most common cause is a typo in column names. Power Query M is case-sensitive. Confirm that the column names in your formula match exactly what appears in your column headers (e.g., New_ERP_GL.Debit not new_erp_gl.debit).

Also check that null has no quotation marks around it. In Power Query, null is a special keyword, not a text string.

Help me fix my Power Query custom column formula

Add the Root Cause Classification and load to Excel

The Match_Status column tells you whether each transaction matches. Now you need a column that explains why mismatches occur. This Root Cause Classification is what makes your reconciliation actionable for a finance team.

  • Click Add Column > Custom Column.
  • Name it Root_Cause_Classification and paste the following formula:
if [New_ERP_GL.TransactionID] = null then "Data Loss"
else if (Number.Abs([Debit_Variance]) > 0 and Number.Abs([Debit_Variance]) < 1) or (Number.Abs([Credit_Variance]) > 0 and Number.Abs([Credit_Variance]) < 1) then "Rounding"
else if [Expected_New_AccountCode] <> [New_ERP_GL.AccountCode] then "Mapping Error"
else if [TransactionDate] <> [New_ERP_GL.TransactionDate] then "Timing Difference"
else "Matched"
  • Click OK.

What does each classification mean?

  • Data Loss: The Legacy transaction has no counterpart in the New ERP at all. It was dropped during migration.
  • Rounding: The amounts differ by less than $1. This indicates a precision issue during data conversion.
  • Mapping Error: The account code in the New ERP does not match the expected mapped code. The translation table was not applied correctly.
  • Timing Difference: The dates differ between the two systems. A transaction posted on January 31 in Legacy may appear as February 1 in the New ERP due to cutover timing.
  • Matched: All fields align perfectly. No action needed.

Why does the order of conditions matter?

Power Query evaluates conditions from top to bottom and stops at the first true result. Data Loss is checked first because if there is no New ERP record, checking amounts or codes would fail on null values. Rounding comes before Mapping Error so that tiny amount differences are not mislabeled as mapping problems.

Now load the completed reconciliation to an Excel sheet.

  • Click the Home tab in the Power Query Editor.
  • Click Close & Load.
  • Excel creates a new sheet with your reconciliation table. Right-click the sheet tab at the bottom and rename it to Reconciliation.
  • Save the workbook as ERP_Reconciliation.xlsx in your ERP-Migration-Reconciliation/excel folder.

Scroll through the loaded table. You should see 60 rows (one per Legacy transaction) with new columns on the right: Expected_New_AccountCode, the expanded New ERP fields, Debit_Variance, Credit_Variance, Match_Status, and Root_Cause_Classification.

What should I see in the table?

Most rows will show "Matched" in both status columns. Look for rows where Root_Cause_Classification shows "Data Loss" (TXN-058 through TXN-060), "Mapping Error" (TXN-046 and TXN-047), "Timing Difference" (TXN-050 through TXN-053), and "Rounding" (TXN-007, TXN-008, TXN-056, TXN-057).

All rows showing Matched?

Confirm that the AccountCode column in both Legacy_GL and Account_Mapping is set to Text type. If Power Query imported them as numbers, "1010" and "1010.0" will not match. Go back into Power Query (Data > Queries & Connections > double-click the query) and fix the types.

Help me debug my Power Query reconciliation results

Your automated reconciliation engine is now complete. Every Legacy transaction is paired with its New ERP counterpart, variances are calculated, and root causes are classified. Next, you will bring this data into Power BI to build a visual dashboard that makes these findings instantly actionable.

Build the Power BI Reconciliation Tracker Dashboard

Your Excel reconciliation model now classifies every transaction as Matched, Data Loss, Mapping Error, Timing Difference, or Rounding. That classification is powerful, but scrolling through rows in a spreadsheet is not how a transition team works.

In this step, you will import the reconciliation data into Power BI Desktop and build an interactive dashboard that instantly surfaces mismatches, quantifies the dollar value at risk, and breaks down root causes by department.

In this step, get ready to:
  • Import data and build a star schema with dimension tables and relationships.
  • Create DAX measures that calculate match rates and variance totals.
  • Build a dashboard page with KPI cards, a donut chart, a conditional-formatted table, and a bar chart.
Import data and build the star schema

A star schema separates facts (the reconciliation transactions) from dimensions (departments, accounts, dates). This structure makes Power BI visuals faster and easier to filter.

  • Press the Windows key to open your search bar.
  • Type Power BI Desktop and press Enter to open it.
  • Close or dismiss any start screen that appears so you see a blank canvas.
  • Save the file immediately by pressing Ctrl+Shift+S.
  • Navigate to your ERP-Migration-Reconciliation\powerbi folder and save the file as ERP_Reconciliation.pbix.

Now import your reconciliation data from the Excel workbook.

  • On the Home ribbon, click Get data.
  • Select Excel Workbook from the list, then click Connect.
  • Browse to ERP-Migration-Reconciliation\excel\ERP_Reconciliation.xlsx and click Open.
  • In the Navigator window, check the box next to the Reconciliation table.
  • Click Load to bring the data into your model.

Why Load instead of Transform Data?

Your Excel workbook already has all the Power Query transformations applied (joins, variance calculations, root cause classification). Loading directly brings in the final reconciled table without re-doing that work.

Next, import the Account Mapping file to serve as your Accounts dimension table.

  • Click Get data again on the Home ribbon.
  • Select Text/CSV and click Connect.
  • Browse to ERP-Migration-Reconciliation\data\Account_Mapping.csv and click Open.
  • In the preview window, click Load.

Now create the Departments dimension as a calculated table and the Date table using DAX.

  • Switch to Table View by clicking its icon on the left edge of Power BI Desktop.
  • In the Home ribbon, click New table in the Calculations group.
  • Enter the following DAX formula in the formula bar to create the Departments dimension:
Departments = SUMMARIZE(Reconciliation, Reconciliation[DepartmentID], Reconciliation[DepartmentName])
  • Press Enter to confirm the formula.
  • Click New table again in the Calculations group.
  • Enter the following DAX formula in the formula bar to create the Date table:
Date = CALENDAR(DATE(2026, 1, 1), DATE(2026, 1, 31))
  • Press Enter to confirm.
  • In the Fields pane on the right, right-click the Date table.
  • Select Mark as date table, then select Mark as date table again from the sub-menu.
  • In the dialog that appears, confirm that the Date column is selected, then click OK.

What do these calculated tables do?

The SUMMARIZE function extracts unique DepartmentID and DepartmentName combinations from the Reconciliation table, creating a clean dimension.

The CALENDAR function generates one row per day for January 2026, which is the period covered by your GL data. Marking it as a date table enables time-intelligence features in Power BI.

Finally, define the relationships between tables.

  • Switch to Model View by clicking its icon on the left edge of Power BI Desktop.
  • Drag DepartmentID from the Departments table onto DepartmentID in the Reconciliation table to create a relationship.
  • Drag Legacy_AccountCode from the Account_Mapping table onto AccountCode in the Reconciliation table.
  • Drag Date from the Date table onto TransactionDate in the Reconciliation table.
  • Press Ctrl+S to save.

You should see four tables in Model View with lines connecting them in a star pattern (Reconciliation in the center, dimensions around it).

Relationship not created?

If Power BI shows an error when dragging to create a relationship, double-check that the data types match between the two columns. You can change a column's data type by clicking it in Table View and selecting the correct type from the Column tools ribbon.

Help me troubleshoot relationship errors

Create DAX measures for the KPIs

DAX measures calculate values dynamically based on the filters applied in your visuals. You need five measures to power the dashboard KPIs.

  • Switch to Report View by clicking its icon on the left edge.
  • In the Fields pane, right-click the Reconciliation table and select New measure.
  • Enter the following DAX formula in the formula bar:
Total Transactions = COUNTROWS(Reconciliation)
  • Press Enter to confirm the measure.
  • Right-click the Reconciliation table again and select New measure.
  • Enter this formula to count matched transactions:
Matched Count = CALCULATE(COUNTROWS(Reconciliation), Reconciliation[Match_Status] = "Matched")
  • Press Enter and create another new measure.
  • Enter this formula to count transactions that did not match:
Variance Count = CALCULATE(COUNTROWS(Reconciliation), Reconciliation[Match_Status] <> "Matched")
  • Press Enter and create another new measure.
  • Enter this formula to calculate the match percentage:
Match Rate % = DIVIDE([Matched Count], [Total Transactions])
  • Press Enter and create the final measure.
  • Enter this formula to sum the dollar value of all variances:
Total $ at Risk = SUMX(FILTER(Reconciliation, Reconciliation[Match_Status] <> "Matched"), ABS(Reconciliation[Debit_Variance]) + ABS(Reconciliation[Credit_Variance]))
  • Press Enter to confirm.
  • Press Ctrl+S to save.

What do these measures do?

COUNTROWS counts every row in the Reconciliation table. CALCULATE applies a filter so you can count only rows where a condition is true (matched or not matched).

DIVIDE safely divides two numbers without producing an error when the denominator is zero. SUMX iterates row by row over the filtered table, summing the absolute variance for each unmatched transaction.

You should see all five measures listed under the Reconciliation table in the Fields pane, each with a calculator icon.

Seeing a formula error?

Check that your column names match exactly, including square brackets and quotes. Column names are case-sensitive in DAX. Also confirm your Reconciliation table has columns named Match_Status, Debit_Variance, and Credit_Variance.

Help me debug my DAX formula

✔️ Awesome, I've got everything!

Great. Make sure you have saved your file before continuing.

ⓧ I'd like to double check the full DAX measures

Here are all five measures together for reference:

Total Transactions = COUNTROWS(Reconciliation)

Matched Count = CALCULATE(COUNTROWS(Reconciliation), Reconciliation[Match_Status] = "Matched")

Variance Count = CALCULATE(COUNTROWS(Reconciliation), Reconciliation[Match_Status] <> "Matched")

Match Rate % = DIVIDE([Matched Count], [Total Transactions])

Total $ at Risk = SUMX(FILTER(Reconciliation, Reconciliation[Match_Status] <> "Matched"), ABS(Reconciliation[Debit_Variance]) + ABS(Reconciliation[Credit_Variance]))
Build the dashboard visuals

With measures ready, you can now create the four visuals that make up your reconciliation tracker. Each visual answers a different question for the transition team.

  • Make sure you are in Report View (leftmost icon on the edge).
  • In the Visualizations pane on the right, click the Card visual icon (a single number card).
  • Drag the Match Rate % measure from the Fields pane into the card visual.
  • Resize the card and position it in the top-left area of the canvas.
  • Repeat: add two more Card visuals, placing Variance Count in one and Total $ at Risk in the other.
  • Arrange all three cards in a row across the top of the page.

What should I see?

Your Match Rate % card should show a decimal value (around 0.78 to 0.82 depending on your reconciliation logic). The Variance Count card shows the number of unmatched transactions. Total $ at Risk shows the cumulative dollar impact of all variances.

Next, add the Root Cause Classifier donut chart.

  • Click an empty area of the canvas to deselect the cards.
  • In the Visualizations pane, select the Donut chart icon.
  • Drag Root_Cause_Classification to the Legend well.
  • Drag TransactionID to the Values well (it will default to Count).
  • Position the donut chart below the KPI cards on the left side of the canvas.

Now add the unmatched transactions detail table with conditional formatting.

  • Click an empty area of the canvas.
  • In the Visualizations pane, select the Table visual icon.
  • Drag these fields into the table: TransactionID, AccountCode, TransactionDate, Debit_Variance, Credit_Variance, Match_Status, and Root_Cause_Classification.
  • With the table selected, add a visual-level filter: drag Match_Status to the Filters on this visual area in the Filters pane.
  • Uncheck Matched so only unmatched transactions appear.

Apply conditional formatting to highlight root causes by color.

  • With the table still selected, find Root_Cause_Classification in the Values well of the Visualizations pane.
  • Click the dropdown arrow next to it and select Conditional formatting, then Background color.
  • In the dialog, change Format style to Rules.
  • Add a rule where the value contains Data Loss and set the background color to red.
  • Add a rule where the value contains Mapping Error and set the background color to orange.
  • Add a rule where the value contains Timing Difference and set the background color to yellow.
  • Click OK to apply.

Why rules-based formatting?

Rules-based conditional formatting applies specific colors based on the text value in each cell. This lets the transition team instantly spot the severity of each mismatch without reading every row.

Finally, add the department breakdown bar chart.

  • Click an empty area of the canvas.
  • In the Visualizations pane, select the Clustered bar chart icon.
  • Drag DepartmentName to the Y-axis well.
  • Drag Variance Count (the measure) to the X-axis well.
  • Position the bar chart on the right side of the canvas, next to the donut chart.
  • Press Ctrl+S to save.

Your dashboard now has all four visuals: KPI cards across the top, a donut chart showing root cause distribution, a color-coded detail table of unmatched transactions, and a bar chart breaking down variances by department.

Donut chart or bar chart is blank?

Make sure you dragged the correct field to the correct well. For the donut chart, Root_Cause_Classification must be in Legend (not Values). For the bar chart, confirm you used the Variance Count measure (calculator icon), not the Match_Status column.

Help me fix my blank visuals

Your reconciliation tracker now gives a transition team a single-page view of data integrity across the entire migration. Next up, you will document this project for your portfolio on GitHub.

Document for Your Portfolio

Your reconciliation dashboard is complete and surfacing real variances. Now it is time to package this work so that hiring managers and stakeholders can see it.

A polished GitHub repository with a clear README transforms a local analysis into a portfolio piece that demonstrates enterprise data governance skills.

In this step, get ready to:
  • Create a GitHub repository with a professional README documenting your project.
  • Upload your dashboard screenshots and project files.
  • Frame the project with business impact and methodology sections.
Create your GitHub repository and write the core README

A README is the first thing anyone sees when they visit your repository. It needs to communicate what you built, why it matters, and what you found.

  • Open your browser and navigate to github.com. Sign in to your account (or create a free account if you do not have one).
  • In the upper-right corner of any page, click the + icon.
  • Click New repository.
  • In the Repository name field, type erp-migration-reconciliation.
  • In the Description field, type Automated ERP migration reconciliation engine with Power Query and Power BI.
  • Select Public so others can view your project.
  • Check the Add a README file checkbox.
  • Click Create repository.

Why a public repository?

Public repositories are visible to anyone on the internet, including recruiters and hiring managers browsing your GitHub profile. This project contains only mock data, so there is no risk in sharing it publicly.

GitHub creates your repo with a blank README.md file. Now you will replace that content with your project documentation.

  • From your new repository's file list, click README.md to open it.
  • Click the pencil icon in the upper-right corner of the file view to open the editor.
  • Delete the existing content and paste the following markdown:
# ERP Migration Reconciliation: Data Integrity Tracker

## Business Context

Data migration is the highest-risk phase of any ERP implementation. When organizations transition from a legacy system to a new ERP, every general ledger transaction must transfer accurately. A single mapping error or dropped transaction can cascade into material financial misstatements.

## Problem Statement

Manual reconciliation of GL data between legacy and new ERP systems takes 40+ hours per migration cycle. Teams compare spreadsheets line-by-line, making the process error-prone and impossible to scale across hundreds of accounts.

## Solution

An automated reconciliation engine built with Excel Power Query and Power BI that:
- Joins legacy and new ERP datasets on TransactionID
- Calculates debit and credit variances automatically
- Classifies every mismatch into a root cause category (Mapping Error, Timing Difference, Data Loss, Rounding)
- Surfaces results in an interactive dashboard for immediate stakeholder review

## Key Findings

Analysis of 60 legacy transactions against the new ERP revealed:
- **3 Mapping Errors**: Legacy account codes (1010, 1200) appeared in the new system instead of mapped codes (110100, 120000)
- **4 Timing Differences**: Transactions dated January 31 in Legacy appeared as February 1 in New ERP
- **3 Data Loss events**: Transactions TXN-058, TXN-059, and TXN-060 were completely absent from the new system
- **2 Rounding variances**: Precision differences of $0.05 and $0.10 during data conversion

## Tools Used

- Microsoft Excel (Power Query) - ETL and reconciliation logic
- Power BI Desktop - Interactive dashboard visualization
- DAX - Calculated measures and KPI logic
- GitHub - Version control and portfolio documentation

What does this README communicate?

  • Business Context tells the reader why ERP migration reconciliation matters to an organization.
  • Problem Statement frames the pain point your project solves (40+ hours of manual work per cycle).
  • Solution describes your automated approach at a high level.
  • Key Findings shows the specific variances your engine discovered, proving it works.
  • Tools Used gives recruiters a quick scan of your technical skills.
  • Click Commit changes in the top-right corner.
  • In the dialog that appears, leave the default commit message and click Commit changes again.

Your repository now shows the formatted README on its front page.

Don't see formatted markdown?

Make sure the file is named exactly README.md (case-sensitive). GitHub renders markdown only for files with the .md extension.

Help me fix my README rendering

Add screenshots, project files, and the Business Impact section

Screenshots give reviewers instant visual proof of your dashboard without needing to download the .pbix file. You will also upload your data files and Power BI file so others can explore your full analysis.

  • Switch back to Power BI Desktop where your dashboard is open.
  • Take a screenshot of your main dashboard page (KPI cards, donut chart, matrix, and bar chart all visible).
  • Save the screenshot as dashboard_overview.png in your local ERP-Migration-Reconciliation folder.
  • Take a second screenshot zoomed in on the conditional-formatted matrix showing the red, orange, and yellow variance rows.
  • Save it as variance_detail.png.

Taking screenshots

Press Cmd+Shift+4 (macOS) or Windows+Shift+S (Windows) to capture a selected area of your screen.

Now upload your project files to the GitHub repository.

  • Return to your erp-migration-reconciliation repository in your browser.
  • Click the Add file dropdown near the top of the page.
  • Click Upload files.
  • Drag and drop the following files from your local ERP-Migration-Reconciliation folder into the browser: data/Legacy_GL.csv, data/New_ERP_GL.csv, data/Account_Mapping.csv, powerbi/ERP_Reconciliation.pbix, dashboard_overview.png, and variance_detail.png.
  • At the bottom of the page, click Commit changes.

Why upload the .pbix file?

The .pbix file lets anyone with Power BI Desktop open your full interactive dashboard. Recruiters who use Power BI can explore your data model, DAX measures, and visualizations directly.

Next, add the Business Impact section to your README.

  • From your repository's file list, click README.md.
  • Click the pencil icon to edit.
  • Scroll to the bottom of the existing content and add the following markdown below the Tools Used section:
## Business Impact

**Data Integrity during Corporate Transformations**

Reduced manual verification from 40+ hours to under 2 hours, catching 100% of mapping errors and data loss events before go-live.

- Automated transaction-level matching eliminates human error in the reconciliation process
- Root cause classification enables targeted remediation instead of blanket re-checks
- Interactive dashboard provides real-time visibility for project stakeholders and auditors

Why frame it this way?

The Business Impact section speaks to decision-makers. Quantifying "40+ hours reduced to under 2 hours" mirrors how finance teams justify automation investments. This framing positions you as someone who understands business value, not just technical implementation.

  • Click Commit changes, then confirm with Commit changes in the dialog.

Your README now shows the quantified business value of your reconciliation engine.

Add the methodology section

A methodology section signals that your approach follows a structured framework, not ad-hoc analysis. This aligns your project with industry best practices for ERP migration testing.

  • From your repository's file list, click README.md and click the pencil icon to edit.
  • Scroll to the bottom and add the following markdown below the Business Impact section:
## Methodology

This project follows a 5-layer reconciliation approach aligned with enterprise data migration testing best practices:

1. **Record count validation** - Verify row counts match between source and target systems
2. **Value reconciliation** - Compare SUM, MIN, and MAX aggregates at the account level
3. **Transaction-level matching** - Join individual records on TransactionID and compare field-by-field
4. **Root cause classification** - Categorize every variance into Mapping Error, Timing Difference, Data Loss, or Rounding
5. **Resolution tracking** - Assign status (Open, In Progress, Resolved, Accepted Risk) to each variance for stakeholder follow-up

This methodology aligns with industry frameworks for ERP migration validation and testing.

What is the 5-layer reconciliation approach?

Each layer catches different types of problems. Record counts catch data loss. Value reconciliation catches systematic drift. Transaction-level matching catches individual mapping errors.

Root cause classification makes the findings actionable. Resolution tracking turns analysis into project management. Together, these layers form a defense-in-depth strategy that mirrors how enterprise data teams validate migrations.

  • Click Commit changes, then confirm with Commit changes in the dialog.

Your repository is now complete with all project files, screenshots, and a professional README.

Screenshots not showing in your repo?

Make sure you uploaded the .png files in the same commit as your other files. If they are missing, click Add file and then Upload files again to add them.

Help me troubleshoot my file uploads

✔️ Awesome, I've got everything!

Great. Double check that your repository shows: README.md with all sections rendered, your CSV data files, the .pbix file, and your screenshot images.

ⓧ I'd like to double check the full README

Here is the complete README.md content for comparison:

# ERP Migration Reconciliation: Data Integrity Tracker

## Business Context

Data migration is the highest-risk phase of any ERP implementation. When organizations transition from a legacy system to a new ERP, every general ledger transaction must transfer accurately. A single mapping error or dropped transaction can cascade into material financial misstatements.

## Problem Statement

Manual reconciliation of GL data between legacy and new ERP systems takes 40+ hours per migration cycle. Teams compare spreadsheets line-by-line, making the process error-prone and impossible to scale across hundreds of accounts.

## Solution

An automated reconciliation engine built with Excel Power Query and Power BI that:
- Joins legacy and new ERP datasets on TransactionID
- Calculates debit and credit variances automatically
- Classifies every mismatch into a root cause category (Mapping Error, Timing Difference, Data Loss, Rounding)
- Surfaces results in an interactive dashboard for immediate stakeholder review

## Key Findings

Analysis of 60 legacy transactions against the new ERP revealed:
- **3 Mapping Errors**: Legacy account codes (1010, 1200) appeared in the new system instead of mapped codes (110100, 120000)
- **4 Timing Differences**: Transactions dated January 31 in Legacy appeared as February 1 in New ERP
- **3 Data Loss events**: Transactions TXN-058, TXN-059, and TXN-060 were completely absent from the new system
- **2 Rounding variances**: Precision differences of $0.05 and $0.10 during data conversion

## Tools Used

- Microsoft Excel (Power Query) - ETL and reconciliation logic
- Power BI Desktop - Interactive dashboard visualization
- DAX - Calculated measures and KPI logic
- GitHub - Version control and portfolio documentation

## Business Impact

**Data Integrity during Corporate Transformations**

Reduced manual verification from 40+ hours to under 2 hours, catching 100% of mapping errors and data loss events before go-live.

- Automated transaction-level matching eliminates human error in the reconciliation process
- Root cause classification enables targeted remediation instead of blanket re-checks
- Interactive dashboard provides real-time visibility for project stakeholders and auditors

## Methodology

This project follows a 5-layer reconciliation approach aligned with enterprise data migration testing best practices:

1. **Record count validation** - Verify row counts match between source and target systems
2. **Value reconciliation** - Compare SUM, MIN, and MAX aggregates at the account level
3. **Transaction-level matching** - Join individual records on TransactionID and compare field-by-field
4. **Root cause classification** - Categorize every variance into Mapping Error, Timing Difference, Data Loss, or Rounding
5. **Resolution tracking** - Assign status (Open, In Progress, Resolved, Accepted Risk) to each variance for stakeholder follow-up

This methodology aligns with industry frameworks for ERP migration validation and testing.

Your project is now documented and publicly accessible. Next up, you will tackle an optional Secret Mission that adds resolution tracking and drill-through functionality to your dashboard.

Secret mission

Resolution Status Tracker with Drill-Through

Your reconciliation dashboard surfaces variances, but a real transition team needs to track resolution progress. In this secret mission, you will add a Resolution Status tracker with a drill-through page that lets stakeholders right-click any variance category and see every affected transaction, color-coded by resolution state.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally with free tools, so there are no ongoing costs.

Resources you used:

  • Local project folder ERP-Migration-Reconciliation in your Documents folder (contains data, excel, powerbi, and docs subfolders).
  • GitHub repository erp-migration-reconciliation.
  • Power BI Desktop (installed application).

Keep everything running

No action needed. Choose this if you are still actively building on this project or plan to extend the dashboard further.

  • Your local files remain in the ERP-Migration-Reconciliation folder.
  • Your GitHub repository stays live for portfolio viewing.
  • Power BI Desktop stays installed and ready for future projects.

Pause - I'll come back to this later

Close any open applications to free up memory. Your files and repository stay intact for when you return.

  • Close Power BI Desktop if it is still open.
  • Close Excel if your workbook is still open.
  • Your local files and GitHub repository remain untouched.

Delete - I don't want to use this again

Remove all project resources and start fresh. This permanently deletes your work.

  • Delete the local project folder by opening File Explorer, navigating to your Documents folder, right-clicking the ERP-Migration-Reconciliation folder, and selecting Delete.
  • Alternatively, open a Command Prompt and run this command:
rmdir /s /q "%USERPROFILE%\Documents\ERP-Migration-Reconciliation"
  • Delete the GitHub repository by navigating to your erp-migration-reconciliation repository on GitHub.
  • Click the Settings tab at the top of the repository page.
  • Scroll down to the Danger Zone section and click Delete this repository.
  • Click I want to delete this repository, then click I have read and understand these effects.
  • Type the repository name to confirm, then click Delete this repository.

To uninstall Power BI Desktop:

  • Press the Windows key to open your search bar.
  • Type Add or remove programs and press Enter.
  • Search for Power BI Desktop in the list, click it, then click Uninstall.

Nice Work!

Nice Work!

You did it. You have built a complete ERP migration reconciliation analytics project from scratch.

You've learned how to:

  • Generate realistic mock general ledger datasets with deliberate discrepancies representing real ERP migration failure modes.
  • Build an automated Power Query reconciliation engine that classifies variances into Mapping Error, Timing Difference, Data Loss, and Rounding.
  • Create a Power BI reconciliation tracker dashboard and document the project in a portfolio-ready GitHub repository.
  • Added a Resolution Status drill-through tracker giving stakeholders full visibility into variance resolution progress.

Ready to quiz yourself?