Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
kweinert
New Member

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
kweinert
New Member

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.

View solution in original post

10 REPLIES 10
kweinert
New Member

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

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

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*

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Top Solution Authors
Top Kudoed Authors