Checking format of an email field

[83dons]

I have a column of 30,000 email addresses. However I have spotted some that have been entered such as "noemail" or "No Email" etc. I kniow hoe to strip white space out of the column using replace with but are there any useful ways of proofing the format of each email and to list any that do not satisfy the crieria of what an email should be - at minimum do they have an @ within them and perhaps a following dot but I guess the @ checker would cast most of my problem ones.

[Rupak_bi]

Hi @83dons ,

To identify @ ,containstring will work.

check = if(containstring(table,colume),"@"),1,0)
same way to search ".", you may club both using an and function inside if statement.

[burakkaragoz]

Hi @83dons ,

Here’s how you can check the format of email addresses in a column and identify those that don’t meet basic criteria (such as containing an "@" and a dot following it):

Power Query Approach

1. Add a Custom Column for Validation

   In Power Query, go to Add Column > Custom Column and use a formula like this to check if an email is valid:

   m

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

   This checks if there is an "@" and at least one "." after the "@", which filters out entries like "noemail" or "No Email".

2. Optional – Use a More Advanced Pattern

   For more robust validation, you can use Power Query’s Text.RegexMatch (available in Power BI Desktop May 2024+):

   m

   if Text.RegexMatch([Email], "^[^@]+@[^@]+\.[^@]+$") then "Valid" else "Invalid"
   

   This pattern ensures there’s something before and after the "@", and at least one dot in the domain part.

3. Filter Invalid Emails

   After adding the custom column, filter your table to show only rows where the result is "Invalid". This will give you a list of problematic email addresses to review or clean up.

---

Summary:

• Use a custom column in Power Query to check for "@" and "." after it.
• Optionally, use a regex for stricter checking.
• Filter for "Invalid" to see all problematic entries.

Let me know if you need a step-by-step with screenshots or want DAX/Excel alternatives!

[83dons]

Hi @burakkaragoz option one looks like it might work but it doesnt account for null values in the the fields. Null is ok in my list. How would you change your function to work with null values?

I also need a separate function that identifies the invalid ones in the list and then changes them to null values. How would I do this using the above code and any additions?

[83dons]

Hi @burakkaragoz I am getting "Expression.Error: The name 'Text.RegexMatch' wasn't recognized. Make sure it's spelled correctly." on option 2. How do I check if I have the right version installed? Or maybe I made a mistake in adding the column.

[burakkaragoz · Accepted solution]

Hi @83dons ,Thanks for your feedback and for sharing the error details!

The error you’re seeing—Expression.Error: The name 'Text.RegexMatch' wasn't recognized—suggests that your version of Power Query does not currently support the Text.RegexMatch function. This function was introduced in the May 2024 update for Power BI Desktop and Power Query Online. If you’re using an older version of Power BI Desktop, or if you’re working in Excel or another tool where Power Query is not up-to-date, this function will not be available.

Here’s how you can check and resolve this:

1. Check Your Power BI Desktop Version

• Go to File > About in Power BI Desktop.
• Look for the version number. If it’s older than May 2024, consider updating to the latest version from the official Power BI site.
• If you’re using Excel, be aware that updates to Power Query functions can lag behind Power BI Desktop.

2. Alternative Regex Solution for Older Versions

If you can’t update, you can use a different approach. While older versions of Power Query don’t have built-in regex, you can still perform basic email validation with nested functions, similar to the first method I shared.

If you need a stricter check and don’t have Text.RegexMatch, you can:

• Use the original logic to check for “@” and “.” after the “@”:
  m

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

• For more advanced validation, consider exporting your data to Excel and using Excel’s REGEXMATCH or FILTER functions (if you have Microsoft 365), or use a dedicated email cleaning tool.

3. Double-Check Formula Typing

If you are on a recent version but still see the error, make sure you typed the function name exactly as Text.RegexMatch (case sensitive, no typos). Also, Power Query custom columns must reference the correct field names.

4. Additional Tips

• If you are limited to Power Query functions, chaining Text.Contains and Text.PositionOf as above will catch the most common invalid entries.
• If you need to apply full regex and can update Power BI, updating is the easiest fix.

---

Summary Table:

Your Environment Approach

Power BI Desktop (May 2024+)	Use Text.RegexMatch
Older Power BI/Excel	Use nested Text.Contains
Need full regex, can’t update	Export to Excel or use a tool

Let me know which environment you’re using and if you need a step-by-step for any of these approaches. If you’re in Excel and want a regex formula, let me know, and I’ll provide an example!

Happy to help further if you get stuck!

[83dons]

@burakkaragoz I am using option 2 and I can get this to work to produce the list of emails with non standard format. Should I put another forum post in for the new problem of trying to change the email on these ones to null?