Forum Discussion

BrandonH's avatar
BrandonH
Frequent Visitor
3 years ago
Solved

Clean Data - Multiple Criteria - Cleaning Strings of Text

Hello Bi Community,    I know what I am trying to accomplish is possible and has been done before. However, I am unable to find an answer specific to my solution requirements.    My data set deal...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    3 years ago

    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 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.

     

BrandonH's avatar
BrandonH
Frequent Visitor
3 years ago

I appreciate your response. From the screen shot your solution looks promising. I will work some this morning to see if I can get that custom regexReplace function piped into my query. I'll hopefully be back to accept the solution. Fingers crossed. 

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
3 years ago

Here's another version

 

 

 

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]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(")", QuoteStyle.Csv), {"A".."H"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Split Column by Delimiter",null, null, (current,test,replacement)=> if Text.At(current,0)="(" then Text.Lower(current) & ")" else current & ")",Table.ColumnNames(#"Split Column by Delimiter")),
    Recombined = Table.CombineColumns(#"Replaced Value1",Table.ColumnNames(#"Split Column by Delimiter"),Combiner.CombineTextByDelimiter(""),"Column1"),
    #"Added Custom" = Table.AddColumn(Recombined, "Custom", each Text.TrimEnd(Text.Range([Column1],Text.PositionOfAny([Column1],{"A".."Z"})),")"))
in
    #"Added Custom"