HOW TO FIND "MISSING" VALUES?

[corbs]

Hello all!!

I am creating a data validation report. 

I have two tables - Source and Target. 

They have a 1-1 relationship. 

I need to create a table visual with the comparison for each field between them and create a Validation with 4 scenarios. 

For "matching" and "not matching", I am good. But I can't find a way to define when is missing in one or another. Can someone please help me?

I can see the data missing in one or another in the table visual by selecting "Show items with no data" option.

Field A Source	Field A Target	Validation
AAAA	AAAA	Matching
AA	BB	Not Matching
AA		Missing in Target
	BB	Missing in Source

[v-junyant-msft · Accepted solution]

Hi @corbs ,

Since you did not provide sample data, I cannot determine if the method is correct:
You can try this DAX:

Validation = 
IF(
    ISBLANK(Source[Field A]), "Missing in Source",
    IF(
        ISBLANK(Target[Field A]), "Missing in Target",
        IF(
            Source[Field A] = Target[Field A], "Matching",
            "Not Matching"
        )
    )
)

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

[amitchandak]

@corbs , Can you share the source table? I am thinking of a Full outer join in Power query merge followed by custom column or a cross-join

Merge Queries, Why not Merge the code: https://youtu.be/YOFs39RCPfQ

Power Query Cross Join| Cartesian Product: https://youtu.be/7MvROGObBYk