Comparing two tables to find differences

[rax99]

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's, CID15789, 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:

[cookieking]

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

[v-lid-msft]

Hi @rax99 ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

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.

[v-lid-msft · Accepted solution]

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.

[Anonymous]

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]

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.

[Nathaniel_C]

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