Forum Discussion

83dons's avatar
83dons
Icon for Helper III rankHelper III
1 year ago

Dealing with nulls when checking email format in a new column

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

[email protected]                        [email protected]

[email protected]                       [email protected]

No Email Address                            null

NoEmail                                           null

[email protected]                       [email protected]

null                                                  null

01234567                                        null

[email protected]                       [email protected] 

 

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!

4 Replies

  • muhammad_786_1's avatar
    muhammad_786_1
    Icon for Solution Supplier rankSolution Supplier

    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

  • This is already solved please delete the entire topic.

    • Anonymous's avatar
      Anonymous
      Not applicable

      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's avatar
        83dons
        Icon for Helper III rankHelper III

        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.