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 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!
Super UserMinor 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*)*"