Apply a change to data in column based on the value of the following column

[83dons]

Hi in another topic I was givn a solution to adding a column that identifies whether an email in the previous column is valid or invalid: 

if Text.Contains([Email], "@") and Text.Contains(Text.Range([Email], Text.PositionOf([Email], "@")), ".") then "valid" else "invalid"

What I require to do is to change the entry to null if the email is of invalid format. For example we see various things such as "No Email", "No email address" or wrongly typed emails with no dot or @ sign. All of these I require to turn to nulls as would rather have them in our system as null than something that is not an email address. I am not sure how to apply this change based on the value of the following column and then to remove the valid/invalid column.

[v-dineshya]

Hi @83dons ,

Thank you for reaching out to the Microsoft Community Forum.

Please follow below steps.

1. Create sample data. please refer snap.

2. Created new column, with below M code in Query editor.

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

3. Refer the output snap and attched PBIX file.

If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[83dons]

Hi @v-dineshya  I dont think that entirely works. When i add a new step to filter to just rows with null within the new custom column then it crashes after a couple of roles. How can I sense check this further? I am expecting to see about 195 rows listed as I have 115 with null already in Main Email Address which should still be null and about 80 ones with invalid email addresses that this should change to null.

The Added Custom row is as follows currently:

= Table.AddColumn(Source, "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 Filtered Rows step is as follows that prdcues the error, it shoud provide a list of 195 with null in the custom column:

= Table.SelectRows(#"Added Custom", each [Custom] = null)

[83dons]

Any ideas for troubleshooting @v-dineshya  ?

[v-dineshya]

Hi @83dons ,

Replace you M code with below code.

= Table.SelectRows(#"Added Custom", each [Custom] = null or [Custom] = "")

or 

= Table.SelectRows(#"Added Custom", each [Custom] is null)

If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[83dons]

Hi @v-dineshya I dont really need the Filtered Rows step it was just there so I could check that the correct amount of nulls were appearing in the new Custom column. That column should be able to be filtered to nulls only without an error row appearing (as this suggests previous step has a mistake in it). I dont understand why it was throwing an error like it was. The code you provided also throws the error below:

[83dons]

On further looking the issue appears to be with the earleir step ('Added Custom') as it produces an error when dealing with null values in the existing Main Email Address field. Not sure you can use text.contains when applying it to a null value? I am sure that is what produces the error currently.

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

I feel that it needs extra logic built in to deal with null values. Any ideas @v-dineshya ? Try adding some null values into your test main email column and you will see for yourself what happens.

[83dons · Accepted solution]

This appears to be the best answer I have seen:

= Table.AddColumn(#"Replaced Value1", "Custom", each 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)

[v-dineshya]

Hi @83dons ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Thank you.

[johnt75]

Add a conditional column which returns the email address if your valid / invalid column is "valid", otherwise return null. Then delete the original column containing the email address and the valid / invalid column.

[andrewsommer]

Table.TransformColumns(
    YourPreviousStep,
    {
        "Email", 
        each if 
            Text.Contains(_, "@") and 
            Text.Contains(Text.Range(_, Text.PositionOf(_, "@")), ".") and 
            not List.Contains({"no email", "no email address"}, Text.Lower(Text.Trim(_)))
        then _ 
        else null
    }
)

Please mark this post as a solution if it helps you. Appreciate Kudos.

[burakkaragoz]

Hi @83dons ,

You can easily update your data in Power Query so that any value flagged as “invalid” in your validation column is converted to null. Here’s how you can do it step by step:

1. Add the Validation Column (if not already done) You already have a formula to check for valid emails. For example:

m

if Text.Contains([Email], "@") and Text.Contains(Text.Range([Email], Text.PositionOf([Email], "@")), ".") then "valid" else "invalid"

Let’s say this new column is called EmailStatus.

2. Replace Invalid Emails with Null Add a new column using the following logic:

• Go to Add Column > Custom Column
• Use this formula:

m

if [EmailStatus] = "invalid" then null else [Email]

This will return null for any email marked as invalid, and keep the original value for valid emails.

3. (Optional) Remove the Validation Column If you don’t need the EmailStatus column anymore, just right-click and remove it.

Summary:

• Use your validation logic to create a status column.
• Create a new column that sets invalid emails to null.
• Remove the helper/status column if you wish.

Let me know if you’d like the exact M code or help handling edge cases!
translation and formatting supported by AI

[83dons]

Hi @burakkaragoz this seems ok apart from it doesnt deal with the fact that the column already has null values in it legitimately. Does the following require edited as null values need to be listed as Valid too, at the moment they are getting no classification?

if Text.Contains([Email], "@") and Text.Contains(Text.Range([Email], Text.PositionOf([Email], "@")), ".") then "valid" else "invalid"