Dealing with nulls when checking email format in a new column

[83dons]

Hi

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.

= 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)

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 (see below) 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 already solved please delete the entire topic.

[v-sshirivolu]

Hi @83dons,

Thanks for the update! If the issue is resolved, kindly accept the solution so the thread can be marked as closed. This helps others in the community find useful answers more easily. 

[83dons]

No you dont understand. I posted this topic several times as it said it was spam. It was resolved on the other thread and this is a duplicate one so please delete this topic entirely. Thanks.

[muhammad_786_1]

Hi @83dons ,

You just need to add a simple null check to your existing formula to avoid errors. I hope this gives you the desired output.

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

Best Regards,
Muhammad Yousaf

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

LinkedIn