cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rax99
Helper V
Helper V

Comparing two tables to find differences

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

 

Table 1

agentIDDateRingtimeTalktimeConnectionIDHoldTime
126/09/201912124CID157890
226/09/20195452CID7824256
326/09/20194111CID8461221
426/09/201900CID484510
526/09/2019714CID692411
626/09/20191659CID2659414

 

Table 2

agentIDDateRingtimeTalktimeConnectionIDHoldTime
126/09/201912124CID157891
226/09/20195452CID7824256
326/09/20194111CID8461221
426/09/201900CID484510
526/09/2019514CID692411
626/09/20191658CID2659414

 

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:

 

DM.PNG

1 ACCEPTED SOLUTION
v-lid-msft
Community Support
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"
)

 

4.PNG

 


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.

View solution in original post

6 REPLIES 6
cookieking
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
v-lid-msft
Community Support
Community Support

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.

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
Community Support
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"
)

 

4.PNG

 


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.

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:

Comparing Two tables.PNG

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
Super User
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
mtable123.PNG

 

mtable12.PNG

 

mtable1.PNG

 

mtable.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Winner of T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Find out who won the T-Shirt Design Challenge and the top 3 finalists.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.