cancel
Showing results for
Did you mean:
Highlighted
Member

## Comparing two tables to find differences

I have 2 tables that should, in theory, be identical;

Table 1

 agentID Date Ringtime Talktime ConnectionID HoldTime 1 26/09/2019 12 124 CID15789 0 2 26/09/2019 5 452 CID78242 56 3 26/09/2019 4 111 CID84612 21 4 26/09/2019 0 0 CID48451 0 5 26/09/2019 7 14 CID69241 1 6 26/09/2019 16 59 CID26594 14

Table 2

 agentID Date Ringtime Talktime ConnectionID HoldTime 1 26/09/2019 12 124 CID15789 1 2 26/09/2019 5 452 CID78242 56 3 26/09/2019 4 111 CID84612 21 4 26/09/2019 0 0 CID48451 0 5 26/09/2019 5 14 CID69241 1 6 26/09/2019 16 58 CID26594 14

However, in reality, appears minor differences in some of the values. basically for each ConnectionID I'm looking for any differences. So all fields should match for the records (date, agentid, ring, talk, and hold times) otherwise flag up the ConnectionID.

From the sample above, ConnectionID'sCID15789, CID69241, and CID26594 are NON-mathches thus, should be flagged (as x or red or whatever in a new column/field)

I have tried using LOOKUP on the ConnectionID, IF statement and even the RELATED function but these do not appear to give what I need. Is there a dax/power m query that works well in this situation? Any pointers would be great.

Below is a visual representation of the data model.

Abbreviated Data Model Illustration:

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Comparing two tables to find differences

Hi @rax99 ,

We can create a calculated column In TableA using following formula to meet your requirement:

``````IsDifferentFromTableB =
IF (
COUNTROWS (
FILTER (
'TableB',
[agentID] = EARLIER ( [agentID] )
&& [Date] = EARLIER ( [Date] )
&& [Ringtime] = EARLIER ( [Ringtime] )
&& [HoldTime] = EARLIER ( [HoldTime] )
&& [ConnectionID] = EARLIER ( [ConnectionID] )
&& [Talktime] = EARLIER ( [Talktime] )
)
) + 0 > 0,
"NO",
"YES"
)``````

Also you could create in TableB:

``````IsDifferentFromTableA =
IF (
COUNTROWS (
FILTER (
'TableA',
[agentID] = EARLIER ( [agentID] )
&& [Date] = EARLIER ( [Date] )
&& [Ringtime] = EARLIER ( [Ringtime] )
&& [HoldTime] = EARLIER ( [HoldTime] )
&& [ConnectionID] = EARLIER ( [ConnectionID] )
&& [Talktime] = EARLIER ( [Talktime] )
)
) + 0 > 0,
"NO",
"YES"
)``````

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Super Contributor

## Re: Comparing two tables to find differences

Hi @rax99 ,
Start at the bottom, merge the two tables, I used ID, then expand the second table using the table name as a prefix, except don't need ID, then the if statement which checks all matches and flags.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Frequent Visitor

## Re: Comparing two tables to find differences

Hello @rax99

Using the following DAX syntax, I created a calculated table that returns the rows from both tables that do not match. I sorted the table by agentID to compare differences, but you could use ConnectionID too.

Input:
NonMatchingTbl = UNION(EXCEPT('Table 1','Table 2'), EXCEPT('Table 2','Table 1'))

Output:

You could also remove one of the of the except functions within the DAX equation, if it doesn't matter which column does not match.  If you were to do this, you would only see the rows that did not match from one table.

The caveat to this solution is you must have the same number of rows in each table.

Community Support Team

## Re: Comparing two tables to find differences

Hi @rax99 ,

We can create a calculated column In TableA using following formula to meet your requirement:

``````IsDifferentFromTableB =
IF (
COUNTROWS (
FILTER (
'TableB',
[agentID] = EARLIER ( [agentID] )
&& [Date] = EARLIER ( [Date] )
&& [Ringtime] = EARLIER ( [Ringtime] )
&& [HoldTime] = EARLIER ( [HoldTime] )
&& [ConnectionID] = EARLIER ( [ConnectionID] )
&& [Talktime] = EARLIER ( [Talktime] )
)
) + 0 > 0,
"NO",
"YES"
)``````

Also you could create in TableB:

``````IsDifferentFromTableA =
IF (
COUNTROWS (
FILTER (
'TableA',
[agentID] = EARLIER ( [agentID] )
&& [Date] = EARLIER ( [Date] )
&& [Ringtime] = EARLIER ( [Ringtime] )
&& [HoldTime] = EARLIER ( [HoldTime] )
&& [ConnectionID] = EARLIER ( [ConnectionID] )
&& [Talktime] = EARLIER ( [Talktime] )
)
) + 0 > 0,
"NO",
"YES"
)``````

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team

## Re: Comparing two tables to find differences

Hi @rax99 ,

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (3,963)