Can I use Power Query to compare whole rows in two different worksheets?

[bpfish36]

I am trying to compare two worksheets which contain the columns Date, Type, Number, Name, Debit, Credit, Balance. Approximately 50% of the rows will be identical in both sheets, however there are going to be rows in either sheet that don't appear in the other. Or there might be rows that are identical in 6 of 7 columns but not all. I need to create a worksheet that shows only the rows that are identical in both tables, another that shows rows that only appear in appear in Table 1 but not Table 2, and vice versa. Eventually I'd like to figure out which rows are nearly identical - i.e. everything the same except maybe the date - and highlight the differences. Is this something I can do with Power Query and if so would somebody be so kind at to tell me the process. Thank you so much for your help.

 

[sandy666]

you can try
merge all columns in the 1st table as additional column
do the same for the 2nd table
merge tables by these columns with inner join, left.anti, right.anti

note: conditional formatting doesn't work with Power Query

 

[bpfish36]

Unless I'm wrong in my thinking, shouldn't the check on the accuracy of the results be Inner Join + Left Anti = Total row in first table and Inner Join + Right Anti = Total rows in second table? My logic here is the number of rows that appear in both tables, plus the number of rows that are unique to a table should equal the total rows in that table. However, I keep getting a result that is more than that number and can't figure out why. Before I put up this post I was doing the merge by asking Power BI to compare all the columns against each other, rather than concatenate and compare the single column, but the results are the same either way. What am I missing?

 

[sandy666]

see this and choose what you want to achieve

Merge queries overview - Power Query

An overview of merge queries operations in Power Query, and the basic principles and common uses.

docs.microsoft.com