How do I filter R expression in PowerBI

Question

Software: PowerBI (free download at https://powerbi.microsoft.com/en-us/downloads/)

R version: R-3.5.3

Here is Animated Gif of the process I am following

I am trying to filter within the R code

Here is my data

GROUP_NAME    
Madison\Manual\02M477-Shift Stick    
Unknown    
Barbie\Instant\08X048    
Earrings\Instant\30Q739    
Barbie\Instant\08X130    
Earrings\Instant\26Q430

I want it to look like this

I tried to add boolean variable isValid (TRUE if regex is matched, FALSE otherwise)

Then I tried to evaluate isValid before adding to dataset (which populates new column Building)

If isValid is TRUE, output digit-digit-letter-digit-digit-digit

else if isValid is FALSE, output " "

# 'dataset' holds the input data for this script
pattern <- "[[:digit:]]{2}[[:alpha:]]{1}[[:digit:]]{3}"
isValid <- function(x) {grepl(pattern , as.character(x), ignore.case=TRUE)}
bldgCode <- function(x) {grep(pattern , as.character(x), ignore.case=TRUE, value=TRUE)}
output <- within(dataset,{if (isValid==TRUE){Building=bldgCode(dataset$GROUP_NAME)} else " "})

Instead I get error, please assist

DataSource.Error: ADO.NET: R script error.
Error in isValid == TRUE : 
  comparison (1) is possible only for atomic and list types
Calls: within -> within.data.frame -> eval -> eval
Execution halted

Details:
    DataSourceKind=R
    DataSourcePath=R
    Message=R script error.
Error in isValid == TRUE : 
  comparison (1) is possible only for atomic and list types
Calls: within -> within.data.frame -> eval -> eval
Execution halted

    ErrorCode=-2147467259
    ExceptionType=Microsoft.PowerBI.Scripting.R.Exceptions.RScriptRuntimeException

In your `within(...)` call, do you mean to use something like `(isValid(GROUP_NAME)==T)`? The error refers to `isValue` not being found, since your function is `isValid`. Also you're just testing whether the function equals true, not whether the output of the function on a variable is true. — Brian, Mar 13 '19 at 21:37
@Brian I corrected my question. I am trying to test whether each rows in the dataset, i.e. `isValid` is `TRUE`. If 1st row of `isValid` is `TRUE`, then it writes the `regex` string to the column in `Building`. If 1st row of `isValid` is `FALSE`, then it writes blank string to the column in `Building`. Hope this explains it. — Marium, Mar 14 '19 at 15:38
You haven't given anyone enough detail to go on. Try reading through this: https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example?rq=1 — Brian, Mar 14 '19 at 16:08

How do I filter R expression in PowerBI

0 Answers0