Forum Discussion
Power BI - How to use Python script to do a search and replace
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!
8 Replies
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)Hi Anonymous ,
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
Hi Anonymous 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.
Hi Anonymous ,
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