PowerBI Regular Expression Using M or Python

Question

I've never used Python inside of PowerBI before, but I have used Python. I'm happy with a Python or M language solution though.

Let's say I have a column that looks like this:

EntryTime
12:00:00 - 01:10:00
01:00:30 - 05:10:50
2020-11-03 R
2010-03-31 R
2020-04-01 R

I want to replace all values that contain this format, and is case insensitive, with NULL values:

yyyy-MM-dd( )+[R] 

How can I implement this in PowerBI using either M or Python? Please be very specific as I have not used Python inside of PowerBI before.

Answer 1

M does not have native functions to perform any Regex operations. You can use Python.

Step 1: Change the column datatype of EntryTime to Text.

Step 2: Go to Transform -> Run Python Script

Step 3: Paste the below code and click ok.

# 'dataset' holds the input data for this script
import pandas as pd
pat = r'\d{4}-\d{2}-\d{2} R'
dataset["New"] = dataset["EntryTime"].str.replace(pat,'')

Step 4: You will get a window with Name and Value like below.

Step 5: Click the Table under Value column. You will get results as shown below.