Summarize differences between 2 tables

[Anonymous]

Hi everyone,

Basicly i am dealing with an issue in a dashboard i am doing.

I have two tables exported from 2 distinct platforms with info about users (user ID, display name, department...).

I made the comparison between both information, to summarize the differences between the data contained in one table versus the other. The main goal is to get data quality on the databases, and i am pointing out the inconsistencies.

For example in one platform i have "display_name", and in the other i have it as well but the platform is accent sensitive, so that´s one of the differences f.e.

I already done it.

Now i want to make a table, summing the whole differences that i find between one another.

Any suggestion?

[v-yetao1-msft · Accepted solution]

Hi @Anonymous 

You can create two tables, record the difference rows between the two tables, and then create a table finally, merge the two difference rows .

Original data :

Tables record difference

Table 1 not in table 2 = EXCEPT('1','2')

Table 2 not in table 1 = EXCEPT('2','1')

Finally merge these two difference tables .

Table = UNION('Table 1 not in table 2','Table 2 not in table 1')

I have attached my pbix file ,you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

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

[PaulDBrown]

I thought you wanted to identify rows not included in each table? (The number of rows in each table is irrelevant to check the differences; they will just be identified as different rows). If you have a different number of columns (or naming) then the measures need refining.

[PaulDBrown]

You can compare tables using:

To get the rows in table 1 but not in table 2:

Diff table 1 vs table 2 = COUNTROWS(EXCEPT(Table 1, Table 2)

To get the rows in table 2 but not in table 1:
Diff Table 2 vs Table 1 = 

COUNTROWS(EXCEPT(Table 2, Table 1)

[Anonymous]

Hi Paul,

It would be a good way, but the tables are not exactly equal in terms of rows. One has X records, the other has Y. But still, there are a lot of rows with equal data.