Forum Discussion

Imrans123's avatar
Imrans123
Advocate V
4 years ago
Solved

Replacing Values based on WildCard

Hi, 

 

Was wondering if anyone had idea about using wildcard entries to replace values in power Query.  I have a column with many rows, each row representing string of text. I want to replace all values there the string inclues a "th" and precedes a number with a blank space. However, I don't want it to replace a string where th doesn't precede a number 

 

For example, if my data set is as follows, 

Name
123th Street
Health

 

Here there are two entries where string contains th, but for 123th Street, the "th" is preceeded by a number. As such the output should be 

Name
123  Street
Health

 

I am able to use find and replace, but that would replace all "th" within the string irrespective of whether it is preceeded by a number or not.

Need Help

6 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    Imrans123  A complete dynamic solution in PQ is possible with JS and regex

     

    https://community.powerbi.com/t5/Community-Blog/Using-JavaScript-in-power-query-for-regex-Part2/ba-p/2132960

     

    https://community.powerbi.com/t5/Community-Blog/How-to-use-JavaScript-inside-power-query-for-data-extraction/ba-p/1632844

     

     

    let
regex=let   
fx=(input)=>
Web.Page(
"<script>
var x='"&input&"'; 
function dynamicReplace(a) {
    var b = a.match(/\d+th/gm); /*checks for a pattern-'th' preceded by digit in a string*/
    if (b == null) {            /*if no match returns the original string*/ 
        return a
    } else {
        return a.replace(/th/gm, '') /*if match replace only 'th' with a blank space*/
    }
};
document.write(dynamicReplace(x));
</script>"){0}[Data]{0}[Children]{1}[Children]{0}[Text]

in
fx,
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyLslQCC4pSk0tUYrViVbySE3MKclQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each regex([Name]))
in
#"Added Custom"

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      smpa01 I totally forgot regex...it was too slow for 20k rows last time🤣

      • AlexisOlson's avatar
        AlexisOlson
        Super User
        4 years ago

        Yeah, you can probably do this in native M but it's going to be something ugly like this:

        Text.Combine(
    List.Transform(Text.Split([Name], " "), each
        let
            TrimLast2 = Text.Start(_, Text.Length(_) - 2)
        in
            if Text.End(_,2) = "th" and
                List.MatchesAll(
                    Text.ToList(TrimLast2),
                    each List.Contains({"0".."9"}, _)
                )
            then TrimLast2
            else _
    ),
    " "
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Imrans123 

     

    I don't see wildcard in M...so the logic is if it contains "Street" then remove "th"? Pay attention to case, and modify accordingly

    = Table.TransformColumns(yourPreviousStep,{{"Column1", each if Text.Contains(_,"Street") then Text.Replace(_,"th","") else _}})

     

    • AlexisOlson's avatar
      AlexisOlson
      Super User
      4 years ago

      The problem with this is that addresses like "123 Fifth Street" and "804 North Street" are very common and these would become "123 Fif Street" and "804 Nor Street".

      It could also have any or none of Road/Way/Avenue/Boulevard/Lane/Drive/Terrace/Place/Court and their corresponding abbreviations instead of Street.