cancel
Showing results for
Did you mean:
Helper V

## 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
Community Support

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.
6 REPLIES 6
Regular Visitor

Have you tried to manage this in the query?

1. Create a new blank query.
2. enter the formula for removing matching rows: = Table.RemoveMatchingRows( Table2 , Table.ToRecords( Table1 ),{"column1", "column2"...})
3. I found if I did not add the columns names, they would be different because of the IDs. This will return a new table with the data that does not match
Community Support

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.
Community Support

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.
Helper II

HI @v-lid-msft  I am facing same question. And I have tried the method you provided. But I want to know if there is any way to highlight the difference for both tables.

Anonymous
Not applicable

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.

Super User

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

Proud to be a Super User!

Announcements

#### Power BI T-Shirt Design Challenge 2023

Submit your creative T-shirt design ideas starting March 7 through March 21, 2023.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors