Forum Discussion
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 v...
Imrans123 A complete dynamic solution in PQ is possible with JS and regex
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"
Imrans123 A complete dynamic solution in PQ is possible with JS and regex
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"
smpa01 I totally forgot regex...it was too slow for 20k rows last time🤣
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 _ ), " " )
