Help with null values in creating a new column

[83dons]

I have a column of emails (called 'Main Email Address'). I have an add column step that works well to check the email addresses for any that do not look like email addresses and if so it just adds a null for them.

The problem is that the initial column ('Main Email Address') also contains nulls which is fine I would like to keep these in the new 'Custom' column however the code above produces an error every time it tries to run on a null email address (I think its to do with text.contains not being exectuable perhaps on a null value). Can anyone suggest what syntax I need to fix this?

Example Input and Output required:

Main Email Address (type text)      Custom (type text)

null                                                  null

null                                                  null

j.bloggs@email.com                        j.bloggs@email.com

joe.bloggs@nhs.com                       joe.bloggs@nhs.com

No Email Address                            null

NoEmail                                           null

joe.blogg@msn.com                       joe.blogg@msn.com

null                                                  null

01234567                                        null

j.blogg@hotmail.net                       j.blogg@hotmail.net 

Thanks for any help. I am sure its something simple I need to add. My first attempt to post this got flagged as spam so trying using code around the emails!

[83dons]

This is the code for the new column:

= Table.AddColumn(#"Replaced Value1", "Custom", each if Text.Contains([Main Email Address], "@") and Text.Contains(Text.Range([Main Email Address], Text.PositionOf([Main Email Address], "@")), ".") then [Main Email Address] else null)

[83dons]

This is the error message image:

[danextian · Accepted solution]

Hi @83dons 

Try the following

let 
check = 
if Text.Contains([Main Email Address], "@") and Text.Contains(Text.Range([Main Email Address], Text.PositionOf([Main Email Address], "@")), ".") then [Main Email Address] else null
try check otherwise null

if [Main Email Address] = null then null else if Text.Contains([Main Email Address], "@") and Text.Contains(Text.Range([Main Email Address], Text.PositionOf([Main Email Address], "@")), ".") then [Main Email Address] else null

[83dons]

@danextian thanks the first one has an error below but the second one works ok when adding a new column.