'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Compare File Versions and Show Changes Using Excel Power Query
Question details
The user needs to compare two versions of an Excel file to identify and display only the data that has changed between them.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Comparing old and new data sets or file versions to find discrepancies and updates.
- Observed behavior
- The goal state is to see a filtered list of only the changed data between two file versions by using Power Query's merge and conditional logic features.
Ensure both versions of your Excel files are accessible, properly formatted as tables, and have a common unique identifier (like an ID column) to accurately match and compare the rows.
Compare File Versions Using Power Query Merge and Custom Columns
Merge both file versions into a single query, apply conditional logic to spot differences, and filter the output to show only the changed records.
By merging queries, you can bring the old and new data together side by side. Adding a custom conditional column allows Excel to automatically detect mismatched values between the two versions, making it easy to filter out the unchanged data.
Open a blank Excel workbook, navigate to 'Data' > 'Get Data' > 'From File' > 'From Workbook', and import both the old and new versions of your file as separate queries.
From the Power Query Editor Home tab, select 'Merge Queries as New'. Choose the old file query as the first table and the new file query as the second. Click the unique identifier column (e.g., ID) in both previews to link them, then click 'OK'.
In the new merged query, click the expand icon in the column header of the second table. Select the columns you want to compare so that the old and new values are positioned side by side.
Go to 'Add Column' > 'Custom Column'. Write a conditional formula to compare the columns, such as: if [OldValue] <> [NewValue] then "Changed" else "Unchanged". Name the column 'Status' and click 'OK'.
Click the filter drop-down on your new 'Status' column and uncheck 'Unchanged' to display only the differences. Remove any unnecessary columns, then click 'Close & Load' on the Home tab to output the final results into your workbook.
Compare Data Easily with WPS Office
While Power Query is effective for advanced database handling, WPS Office offers a free, lightweight, and highly compatible alternative. You can easily compare two worksheets side-by-side or use conditional formatting and VLOOKUP formulas to instantly spot differences between file versions without complex queries.
- 1. Open Both Versions: Launch WPS Spreadsheet and open both the old and new versions of your file.
- 2. Use Conditional Formatting: Select the data range in the new file, go to 'Home' > 'Conditional Formatting' > 'New Rule' to highlight unique or mismatched values against the old data.
- 3. Apply Simple Formulas: Alternatively, use a formula like =IF(A2<>Sheet1!A2, "Changed", "") to instantly mark differences right next to your data.
Frequently Asked Questions
Do I have to rebuild the Power Query every time my file updates?
No, you do not need to rebuild the query. You only need to ensure the updated source file replaces the old one (with the exact same name and file path), then click 'Refresh All' in Excel to update the comparison results.
Why are some differences not showing up in my custom column?
This often happens if there are trailing spaces or formatting differences (like text versus number formats) between the two files. Make sure to use the 'Trim' function on your text columns and match data types in Power Query before completing the merge.
Can I compare multiple columns at once in Power Query?
Yes. You can merge queries based on multiple matching columns by holding down the CTRL key and selecting the columns in the exact same order in both table previews during the merge process.
How do I deal with errors after splitting columns in the query?
If splitting columns generates errors, it is usually due to inconsistent delimiters in your raw data. You can resolve this by adding a step to replace inconsistent characters (e.g., replacing semicolons with commas) before applying the split column function.
