Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
@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"
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 _
),
" "
)
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 _}})
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.