Replacing Values based on WildCard

[Imrans123]

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.

[smpa01 · Accepted solution]

@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...

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

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"

[Vera_33]

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

[AlexisOlson]

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 _
    ),
    " "
)

[Vera_33]

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]

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.

[Vera_33]

@AlexisOlson my bad, did not think thoroughly...