cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kweinert Frequent Visitor
Frequent Visitor

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
kweinert Frequent Visitor
Frequent Visitor

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

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.

10 REPLIES 10
kweinert Frequent Visitor
Frequent Visitor

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

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.

Highlighted

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

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

Super User
Super User

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

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




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

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))")

Super User
Super User

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

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.

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




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

IT WORKED!!!! Thank you!

 

*dance dance dance*

fabianhenzler Frequent Visitor
Frequent Visitor

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

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 Smiley Sad

 

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

Super User
Super User

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

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




fabianhenzler Frequent Visitor
Frequent Visitor

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

This seems quite complicated Smiley Embarassed

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