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

Hi, thanks for checking in. This was a typo on my part as I was creating examples. I will update the OP. 

 

The intended cleaned language here would be:

  • In addition to (d)(1) and (d)(2) please remember to keep hands and feet inside the vehicle at all times. 
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
3 years ago

In that case you can use the first occurrence of a capital letter as the starting mark for your cleaned string.

 

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]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Range([Column1],Text.PositionOfAny([Column1],{"A".."Z"})))
in
    #"Added Custom"

 

That only leaves one scenario (uppercase identifier) unaccounted for.  For that you could add a step to force all identifiers to lowercase first.

  • BrandonH's avatar
    BrandonH
    Frequent Visitor
    3 years ago

    There are times where there is langauge that contains a capital letter withen parenthesis. I didnt give a specific example of this so my mention of this situation is somewhat buried in the post. 

     

    There are times where we have language that looks like this:

    • (a)(1)(C) Why does this language have to nest this way.

    I will look further into splitting by capital letters. That is a great point. One part of the formatting that remains consistent is the upper case letter at the beginning of the language. Thanks.