1

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.

Chicken Sandwich No Pickles
  • 2,953
  • 8
  • 45
  • 92

1 Answers1

4

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

Arun Palanisamy
  • 5,281
  • 6
  • 28
  • 53
  • 1
    I get an error message "formula.firewall: query 'SessionData' references other queries or steps so it may not directly access a data source. Please rebuild this data combination. Any suggestions? – Chicken Sandwich No Pickles Jan 16 '21 at 18:34
  • 1
    The easiest solution to that is to ignore privacy level settings https://stackoverflow.com/questions/51823280 – Alexis Olson Jan 16 '21 at 19:08