Forum Discussion
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
Solution 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 YousafIf this post helps, then please consider "Accept it as the solution" to help the other members find it more quickly.
- 83dons
Helper III
This is already solved please delete the entire topic.
- AnonymousNot 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
Helper 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.