Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 CID782...
Need Help
Show and Tell
Tutorial Requests
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    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.

cookieking's avatar
cookieking
Frequent Visitor
4 years ago

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