Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I need to send my selection using Power BI filter to R script.
Currently, whatever fields we select, those fields are available as dataset inside R script environment.
But, my requirement is not to use the fields but to use the selection of particular row of those fields in R script.
Is there any way to make it possible in Power BI to send only my selection related data into R script instead of whole field?
Thanks in advance
Prashant
Solved! Go to Solution.
You can filter the data before passing it to r
e.g. this filters from a testParam
let Source = List.Generate(()=>10, each _ > 0, each _ - 1), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Column1] = testParam), #"Run R Script" = R.Execute("# 'dataset' holds the input data for this script#(lf)#(lf)#(lf)newdata <- dataset",[dataset=#"Filtered Rows"]), #"""newdata""" = #"Run R Script"{[Name="newdata"]}[Value] in #"""newdata"""
You can also change the code used for r based on params.
e.g. this filters from a testParam by changing the r code.
let Source = List.Generate(()=>10, each _ > 0, each _ - 1), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Run R Script" = R.Execute("# 'dataset' holds the input data for this script #(lf) #(lf)SearchValue = '"&testParam&"' #(lf) #(lf)newdata <- subset(dataset, Column1 == SearchValue )", [dataset=#"Converted to Table"]), #"""newdata""" = #"Run R Script"{[Name="newdata"]}[Value] in #"""newdata"""
Downside is that every time you change the param the script needs applying and the permissions confirming.
Are you selecting using a Slicer? Slicers can work against r visualisations.
However they wont work against ther scripting in the Query editor.
Parameters could be used to filter data in the query editor r scripts.
Hi,
Thanks for your reply.
Can you please explain this "Parameters could be used to filter data in the query editor r scripts."
Prashant
You can filter the data before passing it to r
e.g. this filters from a testParam
let Source = List.Generate(()=>10, each _ > 0, each _ - 1), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Column1] = testParam), #"Run R Script" = R.Execute("# 'dataset' holds the input data for this script#(lf)#(lf)#(lf)newdata <- dataset",[dataset=#"Filtered Rows"]), #"""newdata""" = #"Run R Script"{[Name="newdata"]}[Value] in #"""newdata"""
You can also change the code used for r based on params.
e.g. this filters from a testParam by changing the r code.
let Source = List.Generate(()=>10, each _ > 0, each _ - 1), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Run R Script" = R.Execute("# 'dataset' holds the input data for this script #(lf) #(lf)SearchValue = '"&testParam&"' #(lf) #(lf)newdata <- subset(dataset, Column1 == SearchValue )", [dataset=#"Converted to Table"]), #"""newdata""" = #"Run R Script"{[Name="newdata"]}[Value] in #"""newdata"""
Downside is that every time you change the param the script needs applying and the permissions confirming.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |