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.
Super UserOne 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 Output
And 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.
- ronrsnfld
It 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*)*"
- lbendlin
using custom functions per item (instead of for the entire table) is a great idea and makes the code a bit simpler.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZA9b8MwDET/yiGTBRQF2rFbtnbr1iHIQFsXW7AtORKdwP++9EfXAtr49I68y+VUicM5QqZpIAReFvTkVKAd4VOjKUOesryeri9G1656c/hqY8oSGyIU1EMo5Rg3rnp31dkIhUpP0zwT1J7ENu2Md/geKIUopMWYwgyKdEPmfQ6ZI6OWjz96C/xPZ5H46RgRom0814opxH5BmnVHaAI70fugIcX1/6GV6HEIpn2lNXysmVdo7QGdMWUDb6RaRAmeWzkPdqFZS1PIMEDDyKOGT3msXbaZNvNbd9df", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), Cleaned = (t)=> let s = Text.Split(t,")"), s2 = List.ReplaceValue(s,null, null, (current,test,replacement)=> if Text.At(current,0)="(" then Text.Lower(current) & ")" else current & ")"), s3 = Text.Combine(s2,""), s4 = Text.TrimEnd(Text.Range(s3,Text.PositionOfAny(s3,{"A".."Z"})),")") in s4, #"Added Custom1" = Table.ReplaceValue(Source, each [Column1], each Cleaned([Column1]),Replacer.ReplaceValue,{"Column1"}) in #"Added Custom1"
