Power BI - How to use Python script to do a search and replace

[Ce13]

Hi, 

I would like to use a Python script to do an automatic Search and Replace just like we can do with Excel.

In the following example, I would like to replace all the X by Y, I used the following script:

# 'dataset' holds the input data for this script

text1 = "X"
text1 = text1.replace('X', 'Y')

I do not get any error message but in the end, it's not working, X are still there.

Does anyone has any idea of what is wrong or is there another script that could do a Search and replace via Python in Power BI?

Thanks!

[befat]

Hi there, im facing the same issue when i wanna make my automation data cleaning and after attempting so many code i think this one is gonna work on you too... here's the code 

column_name = 'YourColumnName'
replacement_value = 'NewValue'
dataset[column_name].replace({'OldValue': replacement_value}, inplace=True)

[v-yiruan-msft]

Hi @Ce13 ,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem with yours. Thank you.

Best Regards

[Ce13]

Hi @v-yiruan-msft 

I am still facing the issue but it looks like your script and mine are correct.

There is something wrong with Power BI but I am not able to figure it out.

Thanks

[v-yiruan-msft]

Hi @Ce13 ,

You can try to update the script as follow and check whether it works or not:

text1 = "X"
x = text1.replace("X", "Y")
print(x)

And it is easy to achieve it by using Table.ReplaceValue function in Power Query:

Replace multiple values in Power Query in one step

Best Regards

[Ce13]

Hi @v-yiruan-msft  and thank you for your reply!

I tried to use your script but unfortunately, it's not working for me. I have the same behavior as with the script I used previously.

And thanks for the Power Query process.

My goal with using python is to automate my data cleaning. This is for a report that I run every week and I am basically renaming a bunch of projects. I'd like to automate this part and just run a script to save some time.

[v-yiruan-msft]

Hi @Ce13 ,

Please update the script as below:

# 'dataset' holds the input data for this script
import pandas as pd
dataset['Text'] =pd.DataFrame(dataset['Text'].str.replace('X', 'Y', regex=False))

The original data in my table as shown in below screenshot:

After applied the above script can become the below result:

Best Regards

[Anonymous]

Why on Earth would you want to use Python to do something that's so easy to do in Power Query with the standard tools?

[Ce13]

Hi @Anonymous 

My goal with using python is to automate my data cleaning. This is for a report that I run every week and I am basically renaming a bunch of projects. I'd like to automate this part and just run a script to save some time.