Forum Discussion
Comparing two tables to identify discrepancy
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?
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:
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.