Send filtered data from Power BI to R script

[jainprashant21]

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

[stretcharm]

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.

[jainprashant21]

Hi,

Thanks for your reply. 

Can you please explain this "Parameters could be used to filter data in the query editor r scripts."

Prashant

[stretcharm · Accepted 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.