Forum Discussion
Filtering a text column for a specific pattern
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
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.