Powery Query Editor : Compare two files and delete duplicate rows

Question

I want to compare Master with Last Save and show rows in which data has changed

I have about 16 column

I want to see only the row has different value

Example Master File

Last Save File

Answer 1

Hello Gary07123,

If I understand correctly, you want to compare two files (workbooks) in Microsoft Excel and Remove duplicate rows in a workbook.

Please backup your workbooks before trying this Incase you want to revert back to your original workbooks.

You can compare two workbooks using Excel’s View Side by Side Feature:

Open the workbooks you need to compare.

Click the View tab.

Click View Side by Side. This will pull up both worksheets into smaller windows stacked vertically.

Click Arrange All. This setting lets you change the orientation of the workbooks when they’re displayed side-by-side.

Enable Synchronous Scrolling. Once you have both worksheets open

Click on Synchronous Scrolling to make it easier to scroll through both Excel files line-by-line to check for any differences in data manually.

Remove Duplicates Features take the following steps:

Select the cells you want to check for duplicates.

Click on the Data tab on the top of the screen and then select “Remove Duplicates” in the Data Tools drop-down box.

A small dialog box will appear. If your data has headers, make sure the “My Data Has Headers” box is ticked.

Once the entire table is selected, press “OK” to delete all duplicates.

I hope it helps.

Thanks, Rita.

Answer 2

Hi,

Follow the steps shown in this video - Comparing Tables In Power Query | Power BI Tutorial (enterprisedna.co)

Hope this helps.

Answer 3

Hi

I have tried and it did not remove the rows which have on Table1

Answer 4

Hi,

Share the download link of the MS Excel file with 3 tabs - Master data, last save data and the expected result.