Merge Functions in Financial Reconciliation

Combining columns and rows manually can be time-consuming and complex. That’s where the merge function in Power BI becomes useful. Merging allows you to combine data from two or more tables into a single table based on a common column or key. This process is also known as joining tables which is essential when working with relational data.

Power BI supports several types of joins, including inner joins, left outer joins, right outer joins, and full outer joins—each producing a different result depending on how you want to match and combine the data.

Join Kinds

A join kind specifies how a merge operation is performed. The following are the available join kinds in Power Query:

1. Left Outer Join

• Keeps all the rows from the left table and brings in any matching rows from the right table.
• If there’s no match found in the right table, Power BI still includes the row from the left table — but the columns from the right table will show as null.

Great for:

• Displaying all records from the main dataset (e.g., invoices or transactions), including unmatched ones
• Identifying entries that may or may not have corresponding records
• Tracking all invoices, whether paid or unpaid
• Building comprehensive financial statements that retain all master records
• Creating aging reports or balance due listings

2. Right Outer Join

• Keeps all the rows from the right table and brings in any matching rows from the left table.
• If there’s no match found in the left table, the result still includes the row from the right table — but columns from the left table will show as null.

Great for:

• Retaining all records from the related dataset (e.g., payments)
• Identifying payments without invoices, possibly errors or advance payments
• Reconciling bank transactions or imported entries with missing source data
• Ensuring every payment received is included, even if not linked yet
• Detecting misapplied or undocumented credits

3. Full Outer Join

• Brings in all the rows from both tables.
• If a row exists in one table but not the other, the missing side will show null values.

Great for:

• Combining all records from both tables, matched and unmatched
• Performing complete data audits across systems
• Reconciling general ledger entries vs. bank transactions
• Highlighting both missing and extra entries
• Using in error-checking, control reviews, and forensic accounting

Related Offerings

4. Inner Join

• Returns only the rows that have matching values in both tables based on the join key.
• Rows existing only in one table are excluded from the result.

Great for:

• Showing only matched data between two datasets
• Creating reports of paid invoices or reconciled bank entries
• Filtering out unmatched transactions
• Using in compliance reporting where only verified matches are allowed
• Building clean dashboards focused on confirmed records

5. Left Anti Join

• Returns only rows from the left table that don't have any matching rows in the right table.

Great for:

• Finding records in your main table that are missing matches in the secondary table
• Identifying unpaid invoices or pending claims
• Monitoring outstanding financial entries (e.g., open POs, unreconciled sales)
• Using for follow-ups, alerts, or escalation lists
• Detecting data gaps before generating final financial reports

6. Right Anti Join

• It brings in only rows from the right table that don't have any matching rows from the left table.
• If a row exists in the right table but doesn’t exist in the left, it will be included in the result. All matched rows are excluded.

Great for:

• Detecting records in the secondary table not found in the main table
• Spotting payments or bank entries with no supporting documentation
• Identifying manual transactions or unauthorized records
• Supporting fraud prevention or anomaly detection tasks
• Verifying external data feeds for completeness

Summary Table of Join Types

Conclusion

Understanding Power BI's merge types is crucial for clear, effective, and trustworthy financial data modelling. The correct join type can be used to track unpaid invoices, identify duplicate payments, or reconcile financial transactions across systems—all of which support accurate reporting and save hours of manual effort.

The merge function is essential for finance professionals using Power BI to create informative dashboards and efficient workflows.

🎓 Take the Next Step:

👉 Join our free demo class