Power BI Compare Two Excel Sheets: A Practical Guide

Yes. In Power BI Desktop, load both Excel sheets into Power Query, then use Home > Merge Queries to match rows on a shared key, such as an order ID. Choose a join type to find missing rows, and compare expanded columns to identify changed values.

What comparing Excel sheets in Power BI means

Power BI can compare data from two worksheets in the same workbook or from separate Excel files. The comparison takes place in Power Query, the data preparation tool built into Power BI Desktop. Rather than showing two worksheets side by side like Excel, Power BI lets you combine their records, check which keys match, and build a report from the results.

The method depends on what you mean by compare. You might want to find records present in one sheet but not the other, check whether values changed for the same record, or simply stack two sheets containing the same kind of data. These are different tasks:

Merge matches records between queries using one or more columns, such as a customer ID.
Append adds the rows of one query beneath the rows of another. It is useful when both sheets contain the same type of records and you want one combined list.
Cell-by-cell comparison checks corresponding positions in two grids. Power BI is usually a poor fit for this if the sheets have no stable record identifier or their row order changes.

For most meaningful comparisons, use a merge and match on a stable key rather than assuming row 15 in one sheet corresponds to row 15 in the other.

Prepare the Excel sheets before importing

Clean, consistently structured source data makes the comparison easier to trust. In Excel, convert each data range to a table using Insert > Table, and give the tables clear names under Table Design. Tables are preferable to selecting worksheet ranges because they have headers and can expand as new rows are added.

Before loading the data, check that both sheets use the same meaning and format for the fields you plan to match. For example, an order ID stored as text in one table and as a number in the other may not match as expected. Check for blank keys, extra spaces in text, inconsistent date formats, duplicate records, and headings that differ only slightly, such as Customer ID and CustomerID.

A useful example is a current-orders sheet and a previous-orders sheet. Each contains OrderID, Customer, Date, and Amount. OrderID identifies a record; Amount is a value to compare. If there is no unique ID, consider creating one based on reliable business fields, or ask whether several fields together form a valid key. Matching only on a customer name, for instance, can confuse two orders from the same customer.

Load both sheets into Power BI

Open Power BI Desktop and select Home > Get data > Excel workbook.
Choose the workbook. In Navigator, select the relevant worksheet or, preferably, the named Excel table for each data set.
Select Transform Data to open Power Query Editor rather than loading immediately.
Repeat the import for the second workbook if the sheets are in separate files. If they are in the same workbook, select both tables or sheets in Navigator.

In Power Query Editor, the imported items appear as separate queries in the Queries pane. Rename them with informative names such as CurrentOrders and PreviousOrders so it is clear which data set each query represents. Confirm that the key column has the same data type in both queries. Use the data type icon in the column header to review or change a type.

If a worksheet contains notes, totals, or blank rows around the data, remove those before comparing. Power Query can import a sheet as a broad range, including material that is not part of the actual records. Selecting a well-formed Excel table usually avoids that problem.

Merge the queries on a shared key

In Power Query Editor, select the first query, then choose Home > Merge Queries. Choose the second query in the merge window. Click the matching key column in the first preview and then the corresponding column in the second. For a composite key, hold Ctrl while selecting each additional column, selecting the columns in the same order in both previews.

Choose the join kind that matches the question you need to answer. The available join options include:

Left outer keeps every row from the first query and matching rows from the second. This is a common choice when the first query is the main list you want to review.
Full outer keeps rows from both queries, including records with no match on the other side. It is useful for a complete reconciliation.
Left anti returns rows in the first query that have no match in the second. Use it to find records missing from the second sheet.
Right anti returns rows in the second query that have no match in the first.
Inner keeps only rows with matching keys in both queries.

After selecting the join kind, select OK. The merged result includes a new column containing a nested table from the second query. Select the expand icon in that column header and choose the fields to bring into the result. You can keep the key and the fields needed for checking, rather than expanding every column.

When the merge is ready, select Home > Close & Apply to load the transformed data into the Power BI model. The labels and layout can vary slightly between Power BI Desktop releases, but the merge operation is in Power Query Editor.

Find missing records and compare changed values

To find records that occur in one sheet but not the other, use a left anti or right anti join. For example, select CurrentOrders as the first query and PreviousOrders as the second, then choose Left anti. The result contains current records with no matching OrderID in PreviousOrders. Reverse the query order and use Left anti to find records present only in the previous data.

To check whether fields changed for records that do match, use a left outer or full outer merge, expand the comparison fields from the second query, and compare each version of a field. A practical result table might show OrderID, current Amount, previous Amount, and a status such as Match or Changed. In Power Query, you can create a status field with Add Column > Conditional Column, setting conditions for the relevant values. For more complex rules, a custom column can express the comparison logic in Power Query’s M language.

Decide how blanks should be handled before declaring two values different. For example, a blank value in one sheet and zero in the other may represent a genuine change, or it may reflect different data-entry conventions. Dates can also appear different because one column includes a time component. Define the comparison rule to match the meaning of the data, not just how it looks on screen.

For more predictable text matching, consider cleaning the key columns in both queries before the merge. Power Query offers text transformation options such as trimming whitespace and changing case. Apply the same steps to both sides when appropriate. This helps avoid surprises from inconsistent capitalization or stray spaces, but do not remove characters that distinguish legitimate IDs.

Choose a merge, append, or Excel comparison

Use a merge when two lists describe the same kinds of entities and you need to relate records by an identifier. It is the right starting point for reconciliation, checking missing IDs, or comparing fields for the same customer, invoice, or product.

Use an append when each sheet contains additional rows of the same kind. For example, if January and February sheets have the same columns and represent separate months, appending them creates a longer transaction table. Appending does not match individual records or tell you whether a value changed; it simply combines rows. In Power Query, the append command is available from Home > Append Queries.

Use Excel itself for a simple visual comparison when both sheets have identical layouts, fixed row order, and a small number of cells to inspect. Excel’s formulas or conditional formatting can be quicker for that narrow case. Power BI is more useful when the data needs repeatable cleaning, the comparison must be refreshed, or results need to be explored in a report.

For ongoing reconciliations, consider retaining a comparison status in the loaded data and building a table visual filtered to Changed or Missing records. A report can make the outcome easier to review, but the merge logic still depends on the quality of the source data and key.

Duplicates, refresh, and common problems

A merge does not guarantee one output row per key. If a key appears more than once in either query, matching can produce multiple combinations. For example, two rows with OrderID 105 in the first sheet and three with OrderID 105 in the second can yield six matched combinations. Check whether duplicates are errors, valid detail lines, or evidence that the key needs another column. Do not remove duplicates automatically if each row represents a real transaction.

Other common causes of unexpected results include mismatched data types, blank keys, leading or trailing spaces, and a key that is not unique. Review the merge’s matched and unmatched counts, inspect sample records from each result, and verify that the selected columns represent the same real-world identifier. If you compare text values, decide whether case and punctuation should count as differences and clean both sides consistently.

Power Query stores the transformation steps so you can refresh the comparison when the source data changes. In Power BI Desktop, select Home > Refresh to refresh the model from its sources. If you publish the report to the Power BI service, refreshing there depends on the source location and configured credentials; files on a local computer may require an on-premises data gateway for service refresh. Check the connection and refresh settings for your particular storage location rather than assuming publication alone keeps the data current.

For large workbooks or repeated comparisons, keep the source tables focused and avoid importing irrelevant columns. If Excel files are replaced or moved, the saved source path may need updating. Test refresh after changing file locations and confirm that the report still reflects the expected source files.

Frequently Asked Questions

Can Power BI compare two sheets in the same Excel file?
Yes. Select both sheets or, preferably, both named tables in Navigator when connecting to the workbook. Load them as separate queries, then merge them in Power Query using a shared key.

Can Power BI show which rows are missing from either sheet?
Yes. Use a left anti join to find rows in the first query with no match in the second. Reverse the query order and repeat to find rows that exist only in the second. A full outer join is useful when you want both sides represented in one reconciliation.

Can Power BI identify changed cells?
It can compare selected fields for records matched by a key, and you can create a status column or report showing differences. It does not automatically infer that two rows correspond just because they occupy the same worksheet position.

What if the sheets do not have a common ID?
Identify a reliable field or combination of fields that uniquely represents a record. If no trustworthy key exists, a merge may produce incorrect matches. You may need to clean or redesign the source data before comparing it.

Will the comparison update when the Excel sheets change?
Yes, when the source files remain accessible and Power BI refreshes successfully. In Desktop, use Home > Refresh. A published report needs working source credentials and, for some local-file setups, a configured gateway.

Should I use Merge Queries or Append Queries?
Choose Merge Queries to match records and compare their fields. Choose Append Queries to stack rows from similarly structured sheets into one table. Appending alone does not identify missing or changed records.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.