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

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*)*"

 

 

  • BrandonH's avatar
    BrandonH
    Frequent Visitor
    3 years ago

    Hi there, 

     

    In addition to your accepted solution. I imported some new data and there are 2 stragglers that arent being cleaned. I have been trying to learn some RegEx in the interim and cannot get the groupings to properly capture the new issues.

     

    I have strings of text that are preceeded by a 'tab' the string looks like this:

    • "      (a) You will be home no later than 10PM"

    Secondly, I have an issue that I posted about here. I am using your function and RegEx expression to then clean a second column. In this column there is a string of text that looks like this:

    • "Why is this happening."

    When in actuality posting the cell text into Notepad it looks like this:  

    • """Why is this happening."""

    I created a new post as it is an issue running in parallel to my original ask here. 

     

    Any input is appreciated. Take care. 


    [When posting this reply and my new post I received the following error:
    Your post has been changed because invalid HTML was found in the message body. The invalid HTML has been removed. Please review the message and submit the message when you are satisfied. I am wondering if there is some hidden HTML issue going on? I am out of my depth.]

    • ronrsnfld's avatar
      ronrsnfld
      Icon for Super User rankSuper User
      3 years ago

      With regard to your first question, following the "beginning of string character: ^ ", insert an optional group for either a whitespace character or a double quote.  So in the regex you would replace ^ with something like ^(?:[\s"])* or, in javascript,^(?:[\\s"])*

       

      The question about seeing single set of double quotes in one program and the doubled sets in another has to do with how different programs interpret text input. What you see in Notepad++ is what is actually stored.

       

      The "invalid html" message is specific to this forum and it's posting rules. Perhaps if you post the relevant lines as code you may be able to bypass it, but I've not played around with that "feature" enough to be certain.