Forum Discussion
Clean Data - Multiple Criteria - Cleaning Strings of Text
One way to do this is with Regular Expressions. In Power BI Desktop (or Excel) you can write a custom function that uses Javascript.
// regexReplace (text as nullable text,pattern as nullable text,replace as nullable text, optional flags as nullable text) => let f=if flags = null or flags ="" then "" else flags, l1 = List.Transform({text, pattern, replace}, each Text.Replace(_, "\", "\")), l2 = List.Transform(l1, each Text.Replace(_, "'", "\'")), t = Text.Format("<script>var txt='#{0}';document.write(txt.replace(new RegExp('#{1}','#{3}'),'#{2}'));</script>", List.Combine({l2,{f}})), r=Web.Page(t)[Data]{0}[Children]{0}[Children], Output=if List.Count(r)>1 then r{1}[Text]{0} else "" in OutputAnd then use it in your main query like this:
let //Change next line to reflect actual data source Source = Excel.CurrentWorkbook(){[Name="Table16"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), //Javascript regular expression code #"Added Custom" = Table.AddColumn(#"Changed Type", "Cleaned", each fnRegexReplace([Column1], "^(?:(?:\\(([^)]*\\))\\s*)|([a-z0-9](?:[0-9])?[.]\\s*)\\s*)*", "", "img"), type text) in #"Added Custom"You could also use Python and/or R for your Regex if you are using Power BI Service. Just need to check to make sure your package is supported there.
Hi there, finally had some time to try your function. I am unable to get it working. It is admittedly probably my lack of understadning and not having used custom fuctions much.
I started with a table to mirror your screen shot above. To make sure I understood what was happening before moving to my working datasets. The function is not cleaning the same way. Would you mind giving me some more granular steps.
Here is a picture of my output.
Super UserIt appears you renamed the function "Cleaned" instead of "fnRegexReplace". Although allowable, I'd think that a bit confusing since that is also the name of the column being added.
I also see that you did not copy the code as posted. In particular, you did not include the "Pattern" argument in your function call from your main code block. You have it as "null". If you examine the code I posted, you will see the Regular Expression pattern. If you omit it, the function will not do anything.
Hi there ronrsnfld,
I have been able to test the regex clean up function on some test data as well as my working dataset. I have looked through approximately 960K rows of cleaned text and see one instance where the regex clean up is removing extra characters.
In cases where language is something like this:- (b) A current edition of a dictionary is preferred.
The cleaned text is:
- current edition of a dictionary is preferred.
Any language that starts with the "word" 'A' is being treated as a single letter and removed. The words "An", "As" etc aren't being removed.
This happening is effecting 300 or so records out of 960K records.
Is there an update that can be made to the regex cleaning function? I am also looking at creating a custom cleaning rule that says if the language starts with an lower case letter then add "A " to the beginning of the language.
Once you have a chance to weigh in on this I will be accepting your solution. 🙂 apprecaite it so much! Guess I will need to learn about custom functions more and regex!- ronrsnfld
Minor change in the regex. I have edited my answer to correct that in situ. The corrected regex is:
"^(?:(?:\\(([^)]*\\))\\s*)|([a-z0-9](?:[0-9])?[.]\\s*)\\s*)*"Hi there,
In addition to your accepted solution. I imported some new data and there are 2 stragglers that arent being cleaned. I have been trying to learn some RegEx in the interim and cannot get the groupings to properly capture the new issues.
I have strings of text that are preceeded by a 'tab' the string looks like this:
- " (a) You will be home no later than 10PM"
Secondly, I have an issue that I posted about here. I am using your function and RegEx expression to then clean a second column. In this column there is a string of text that looks like this:
- "Why is this happening."
When in actuality posting the cell text into Notepad it looks like this:
- """Why is this happening."""
I created a new post as it is an issue running in parallel to my original ask here.
Any input is appreciated. Take care.
[When posting this reply and my new post I received the following error:
Your post has been changed because invalid HTML was found in the message body. The invalid HTML has been removed. Please review the message and submit the message when you are satisfied. I am wondering if there is some hidden HTML issue going on? I am out of my depth.]