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.

     

ronrsnfld's avatar
ronrsnfld
Icon for Super User rankSuper User
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"