The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
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
Solved! Go to 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.
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.
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
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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))")
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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?
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
This seems quite complicated
Do you have an example for me to follow by any chance?
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 😉
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
IT WORKED!!!! Thank you!
*dance dance dance*