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

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.