Comparing two tables to identify discrepancy

[arcall]

Hello,

We are currently in the migration of data from one system to another and we would like to compare the data from both systems for future conciliation.

I am able to get the data in PowerBI in two distinct tables. The number of columns is 50 for both and the column name have exactly have the same name.

How can I compare these two tables to find differences ? 

Thank you. 

I have been looking around for answers and wasn't unable to find any solution.

[PaulDBrown · Accepted solution]

@arcall 

That's a tricky question, given that each row is treated as unique. In other words, as far as the example posted, the rows identified are to all intense and purposes rows which are present in one table but not the other. 

The question I gess is: is there a row ID in the original dataset which is kept in the new dataset for the supposedly equivalent row? Or can you include one?
For example, for these two rows:

If there is a row ID in Table1 which is kept when Table2 is created, then we can compare them. Otherwise there is no way of knowing if it is a completely new row which has nothing to do with the original. Does that make sense?

[PaulDBrown]

@arcall 

Following this example:

1) Create a new table to identify non-coinciding rows using:

Comparing tables = 
VAR Table1vs2 = EXCEPT(Table1, Table2)
VAR Table2vs1 = EXCEPT(Table2, Table1)
Return
UNION(Table1vs2, Table2vs1)

2) To identify which table each row is from, create a measure:

From Table = 
VAR Table1 = COUNTROWS(INTERSECT(Table1, 'Comparing tables'))
RETURN
IF(Table1 = 1, 1,2)

Now create a table visual with the fields from the new "Comparing Tables" table and add the measure:

[arcall]

Hi, Thanks for your solutions. It's working. However, is there a way to identify which column has changed in the row ? Otherwise, I have to check the 50 columns.

Thank you.

[v-easonf-msft]

Hi， @arcall 

I'm not  clear what your expected result.

Do you expect to return to a new table, this new table lists all the values in table one that are different from those in table two?

Best Regards,
Community Support Team _ Eason

[arcall]

Yes, a table. Or any other ways to rabidly pinpoint where there are differences