Create relationship between 2 queries and identify nulls

[lornafnb]

hi all,

I have 2 data sets - my "main" data set called Friends :

Friends	Surname
John	White
Joe	Black
Mary	Simmonds

and the second one is Friends Birthdays: (note their is an intentional spelling mistake in Jeo's name :)-

Friends	Birthday
John	01-Feb-22
Jeo	05-May-22
Mary	12-Dec-22

In my model I have created a relationship between these two sets, based  on field "Friends". I would like to identify the fields that link up AND those who dont. So I'm thinking of creating a column in my Main table ("Friends") that 

1) contains the value of Birthday if there is a sucessful join AND

2) a string "No Corresponding record found" if the join returns a null.

1). Is there a simple way to do this other than creating a calculated/custom column?

2) otherwise - the code to populate a new calculated/custom for this operation.

Maybe I could Merge the 2 tables and somehow identify the columns? I'd need some assistance to do that.

Thanks

Much appreciated.

[Vijay_A_Verma]

Use this code in a custom column

= try #"Friends Birthdays"{[Friends=[Friends]]}[Birthday] otherwise "No Corresponding record found"

[ddpl · Accepted solution]

Hi,

If you dont want to create calculated or custom column then you can replace null value with "No Corresponding record found" after merging the table.