How can I identify value mismatch across 3 tables even if the Primary Key is a match?

[Jadegirlify]

Table Dev contain column names Test ID, Emp ID, Name, Test Date, expiration, Test Type
Values:
1111, 2345, Tim Smith, 1/2/2024, 1/2/2026, TB
2111, 2346, Jo Ann, 1/2/2023, 1/2/2025, TB
3333, 2347, Lee Ann, 10/2/2022, 10/2/2024, RB
4444, 2348, Sam Ann, 10/2/2022, 10/2/2024, Ly

Table Prod contain column names Test ID, Emp ID, Name, Test Date, expiration, Test Type
1111, 2345, Tim Smith, 1/2/2024, 1/2/2026, TB
2111, 2346, Jo Ann, 1/2/2023, 1/2/2025, RB
3333, 2347, Lee Ann, 10/2/2022, 10/2/2024, RB
4444, 2348, Sam Ann, 10/2/2022, 10/2/2024, Ly

Table EQ contain column names Test ID, Emp ID, Name, Test Date, expiration, Test Type
1111, 2345, Tim Smith, 1/2/2024, 1/2/2026, TB
2111, 2346, Jo Ann, 1/2/2023, 1/2/2025, TB
3333, 2347, Lee Ann, 10/2/2022, 10/2/2024, RB
4444, 2348, Sam Ann, 10/2/2022, 10/2/2024, Ly

The only mismatch between all 3 tables given below is Test ID 2111 where Test Type is RB in Prod 2 and "TB" in the other two tables.

If I have thousands of records in 3 different tables that are expected to have 100% same records/value and it's been identified that some of the values might not be the same in a few columns as shown below, how can I validate/identify the mismatch in powerbi?

See example below where identifying the mismatch is reflected in only Test ID 2111. Thanks 

[Anonymous · Accepted solution]

Hi @Jadegirlify ,

I think you can try to create a check table to help you figure out.

Check Table =
VAR _TESTID =
    DISTINCT (
        UNION (
            VALUES ( Dev[Test ID] ),
            VALUES ( Prod[Test ID] ),
            VALUES ( EQ[Test ID] )
        )
    )
VAR _DEVKEY =
    ADDCOLUMNS (
        Dev,
        "Combinekey",
            COMBINEVALUES (
                "-",
                [Test ID],
                [Emp ID],
                [Name],
                [Test Date],
                [expiration],
                [Test Type]
            )
    )
VAR _PRODKEY =
    ADDCOLUMNS (
        Prod,
        "Combinekey",
            COMBINEVALUES (
                "-",
                [Test ID],
                [Emp ID],
                [Name],
                [Test Date],
                [expiration],
                [Test Type]
            )
    )
VAR _EQKEY =
    ADDCOLUMNS (
        EQ,
        "Combinekey",
            COMBINEVALUES (
                "-",
                [Test ID],
                [Emp ID],
                [Name],
                [Test Date],
                [expiration],
                [Test Type]
            )
    )
VAR _Final =
    ADDCOLUMNS (
        _TESTID,
        "All match?",
            VAR _KEY1 =
                MAXX ( FILTER ( _DEVKEY, [Test ID] = EARLIER ( [Test ID] ) ), [Combinekey] )
            VAR _KEY2 =
                MAXX ( FILTER ( _PRODKEY, [Test ID] = EARLIER ( [Test ID] ) ), [Combinekey] )
            VAR _KEY3 =
                MAXX ( FILTER ( _EQKEY, [Test ID] = EARLIER ( [Test ID] ) ), [Combinekey] )
            RETURN
                _KEY1 = _KEY2
                    && _KEY1 = _KEY3
                    && _KEY2 = _KEY3
    )
RETURN
    _Final

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.