How to validate that two data sources have identical fields and field values?

[Anonymous]

Hi all.

I have two different data sources with about ~30 fields each. I quite simply want to confirm whether the same fields and field values exist in both sources.

For example, if Table A has a field called "Color" with field values of "Red", "Green", "Blue", and Table B also has the same field with the same values, then I want to know that. If the two tables have any discrepancy, such as Table B having the options "Red", Orange", "Blue", then I want to know. Additionally, if Table B doesn't have a field named "Color" at all, then I want to know.

This exercise needs to be done for all ~30 fields in the first data source. Is there a clean and efficient way to do this? I have the two data sources ready and loaded. Thanks.

[Anonymous]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[amitchandak]

@Anonymous , except, intersect

https://docs.microsoft.com/en-us/dax/intersect-function-dax

https://docs.microsoft.com/en-us/dax/except-function-dax

except(all(table[col1),all(table[col1]))

[Anonymous]

[amitchandak]

@Anonymous , The code was for table

new table = except(all(table1[col1),all(table2[col1]))

use in measure

calculate(count(Table[Col1]), filter(Table1, Table[Col1] in except(all(table1[col1),all(table2[col1])))

In measure you might have to use all(Table) or allselected(Table)

[Anonymous]

Would I need to create a new table for each column to verify? I have about 30 columns.

[Anonymous · Accepted solution]

Hi @Anonymous 

If you want to find the difference between two tables, you could use merge and append in power query editor.

TableA:

TableB:

Merge TableA and TableB Left anti.

Result:

Merge TableB and TableA Left anti.

Result:

Append:

Result:

If this reply still couldn't help you to solve this problem, please show me more details. You can provide a sample (your table A and tableB) to me and show me the result you want, it may be easier for me to understand your requirement.

You can download the pbix file from this link: How to validate that two data sources have identical fields and field values?

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.