How to pass query parameter to Data Query (R integration)?

[kweinert]

Is it possible to pass parameters to R data queries? For instance, my query is

let
    Quelle = R.Execute("NormalNoise <- data.frame(x=rnorm(100, mean=0, sd=1)"),
    EnvVars1 = Quelle{[Name="NormalNoise"]}[Value]
in
   EnvVars1

Now, is it possible to pass parameters to the string in R.Execute? For instance, I want to define a parameter NoiseMean and NoiseSd in the Query Window and use their value.

EDIT: I tried to use the "&" operator as I know it from VBA:

let
    Quelle = R.Execute("NormalNoise <- data.frame(x=rnorm(100, mean="&NoiseMean&", sd=1)"),
    EnvVars1 = Quelle{[Name="NormalNoise"]}[Value]
in
   EnvVars1

however, I get the error message that "&" can not applied to text and numbers

[kweinert · Accepted solution]

I found the solution on the net

let
    Quelle = R.Execute("NormalNoise <- data.frame(x=rnorm(100, mean="&Text.From(Parameter1)&", sd=1))"),
    EnvVars1 = Quelle{[Name="NormalNoise"]}[Value]
in
   EnvVars1

does the trick.

[FrugalEconomist]

Thanks for sharing this. Was wondering if I could get your thoughts on my problem.

I've trying to pass a parameter as a variable in my RScript (e.g. my FacebookToken).

It doesn't seem to recognize my variable after using the & & structure you recommended. My parameter is called "FacebookToken"

----------------------------

library(Rfacebook)
data = getInsights(object_id="IDIDIDIDIDIDIDIDID" 
,token = &Text.From(FacebookToken)&
,metric = 'page_impressions'
,period = "day"
, parms = "&since=2014-01-01"
, version = 2.8
)

-----------

Error: 

unexpected '&' in:
"data = getInsights(object_id="47270427529"
,token = &"
Execution halted

[ImkeF]

You need to "wrap" your R-code into an R.Execute-command like shown above, then it should work.

Might be easier if you use the "RunRScript"-button in Transform -> Scripts.

[FrugalEconomist]

Thanks ImkeF.

This is what I have now and it's not working yet, but any idea what all the #(lf) are?

= R.Execute("library(Rfacebook)#(lf)data = getInsights(object_id=""IDIDIDIDIDIDIDID"" #(lf)

           ,token = "&Text.From(FacebookToken)&"#(lf)

           ,metric = 'page_impressions'#(lf)

           ,period = ""day""#(lf)

           , parms = ""&since=2014-01-01""#(lf)

           , version = 2.8#(lf))")

[ImkeF]

Yes, #(lf) is the sign for linefeeds. You can delete them.

Problem with your code now are the escape signs:"

If you have them in your original code (which I don't fully understand, as I'm an R-rookie), you might need to wrap them into "", so that you end up with 3 of them: """. Or sth similar - you might need to play around with it.

[fabianhenzler]

I use a Dropbox hosted script to source() the script which includes the parameters 

username = "&Text.Form(username)&";
password = "&Text.Form(password)&";

when I run it like this - it's just not working 😞

But I'm using it inside a .r Script to load the data. Any Idea?

[ImkeF]

You cannot pass variables to an R-script but just a table as the "dataset".

So you have to create a table in M with those 2 variables and pass that to the R-script instead and then pick the figures from there using R.

[fabianhenzler]

This seems quite complicated 

Do you have an example for me to follow by any chance? 

[ImkeF]

I was wrong - it's possible. Just that the dataset that goes as the initial input into the R-script has to be a table. But on top of that one can pass parameters to it.

Maybe the problem with your script is simple spelling - it needs to be: Text.From instead of Text.Form  😉

[FrugalEconomist]

IT WORKED!!!! Thank you!

*dance dance dance*