Search code examples
pythonpowerbipowerbi-desktopm

PowerBI Regular Expression Using M or Python


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.


Solution

  • 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.

    enter image description here

    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.

    enter image description here

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

    enter image description here