Forum Discussion
Filtering a text column for a specific pattern
Hi all,
I have a text column containing part numbers. There are many different patterns for the part number, some containing just numbers, some having numbers separated by a hyphen, different lengths etc. 12345-678, ABCDEF, ABC-1234, 9876543, ZYXWVUT ...
I am trying to filter just those that have the specific format : first 3 characters are always letters followed by a hyphen followed by 4 numbers and nothing else after that eg ABC-1234.
I have explored SEARCH, FIND, LEFT, RIGHT, ... but none are able to filter just the part numbers I am lookign for. Is there a way to filter based on a text template, or some other way to achieve what I am lookign for?
All help and suggestions greatefully received.
Best bet is to create custom function. But it requires iterating through string, not particulary smart or easy to do.
I'd suggest using R Script in the query editor.
pattern <- "^[[:alpha:]]{3}\\-\\d{4}" isMatch <- function(x) {grepl(pattern, as.character(x), ignore.case=TRUE)} return <- within(dataset,{Flag=isMatch(dataset$PartNumber)})Replace "PartNumber" with your actual column name.
return.
Then it's simply filtering based on "Flag" column.
Edit: You can find RegEx pattern cheat sheet for R in link below.
https://www.rstudio.com/wp-content/uploads/2016/09/RegExCheatsheet.pdf
5 Replies
Best bet is to create custom function. But it requires iterating through string, not particulary smart or easy to do.
I'd suggest using R Script in the query editor.
pattern <- "^[[:alpha:]]{3}\\-\\d{4}" isMatch <- function(x) {grepl(pattern, as.character(x), ignore.case=TRUE)} return <- within(dataset,{Flag=isMatch(dataset$PartNumber)})Replace "PartNumber" with your actual column name.
return.
Then it's simply filtering based on "Flag" column.
Edit: You can find RegEx pattern cheat sheet for R in link below.
https://www.rstudio.com/wp-content/uploads/2016/09/RegExCheatsheet.pdf
Genius, many thanks Chihiro.
I had not played with R before, but installed, ran your script and it worked first time.
I did notice that after running the script it seemed to have created 2 rows with ")" in the PartNumber field. I have deleted these rows but not sure how they get there. They are definitely created by running the R script as they are not there before.
You are welcom. I can't think of any reason why it would create extra rows...
R is very powerful tool and compliments PowerBI very well.
Hi, thanks for you solution. what if I'd like to apply it to multiple columns? thanks in advance
