Forum Discussion
Helper IIIHelp with null values in creating a new column
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
[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!
Hi 83dons
Try the followinglet 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 nullif [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
4 Replies
83donsThis 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
Super User
Hi 83dons
Try the followinglet 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 nullif [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