Using regular expressions in power bi desktop

[V-lianl-msft]

Scenario:
In Power BI, when we want to process complex and unordered data, adding many steps using power query may not get the desired results. As we know, power query cannot use regular expressions. If we call Python script and use regular expressions to process the data this time, it will become simple.

Table Used:

As shown in the figure above, if the data has been imported into Power BI, we can't process the data effectively by using power query. At this time, we can call the regular expression of Python's re module.

Precondition:
To run Python scripts in Power BI desktop, you need to install Python and two Python packages (pandas and matplotlib) on your local computer. Please refer to this document for more details: Run Python scripts in Power BI Desktop.

Operation:
After importing the data into power bi desktop, we can run the python script in IDE and copy it to the python script editor in the pbi query editor after confirmation.

import re
import json

# Custom function to get e-mail address
def get_find_emails(text):
emails = re.findall(r"[a-z0-9\.\-+_]+@[a-z0-9\.\-+_]+\.[a-z]+", text)
emails=';'.join(emails)
return emails

# Custom function to get phone number
def get_find_mobiles(text):
mobiles = re.findall(r" [1-9][0-9]{4,} ", text)
mobiles =';'.join(mobiles)
return mobiles

email_list=[]
tele_list=[]
for i in range(len(dataset)):
text=dataset.iat[i,1]
email=get_find_emails(text)
email_list.append(email)
tele=get_find_mobiles(text)
tele_list.append(tele)

dataset['email']=email_list
dataset['tele']=tele_list

This code defines two functions: get_ find_ Email(Custom function to get phone number) and get_ find_ Mobile(Custom function to get phone number), which will get two lists and eventually place them into the data table.
By the way, the use of regular expressions, you can search for the related resources online to learn about it.
Click OK to return the result:

After expanding the table, the next two columns are the phone numbers and e-mail addresses we want to get.

Please check the attached files for details.

Author: Liang Lu
Reviewer: Ula & Kerry

Using regular expressions in power bi desktop.pbix