Forum Discussion

RMel's avatar
RMel
Frequent Visitor
6 years ago

How to run an R script transformation on an existing query?

Hi all, I am new to Power BI but fairly experienced with R.

 

I am training myself by working through the exercises in the Dashboard in a Day course here: https://powerbi.microsoft.com/en-us/diad/

 

I noticed there is an option in the Query Editor Transform bar to run an R script to transform data instead of/as well as using the GUI.

 

So I tried to create a very simple new column using the R script:

 

dataset$double_revenue <- dataset$Revenue*2

 

This resulted in the error message:

 

"Formula.Firewall: Query 'Sales' (step 'Run R Script') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."

 

The automatically-generated M code for the query is as such (This is my first experience with M so do not know how to debug this):

 

let
Source = Csv.Document(File.Contents("C:\Users\mydata\Data\USSales\sales.csv"),[Delimiter=",", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ProductID", Int64.Type}, {"Date", type date}, {"Zip", type text}, {"Units", Int64.Type}, {"Revenue", type number}}),
#"Appended Query" = Table.Combine({#"Changed Type", #"International Sales"}),
#"Added Conditional Column" = Table.AddColumn(#"Appended Query", "CountryName", each if [Country] = null then "USA" else [Country]),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Country"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"CountryName", "Country"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Country", type text}, {"Revenue", Currency.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each true),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Date.IsInPreviousNYears([Date], 3)),
#"Run R Script" = R.Execute("# 'dataset' holds the input data for this script#(lf)#(lf)dataset$double_revenue <- dataset$Revenue*2",[dataset=#"Filtered Rows1"])
in
#"Run R Script"

 

I have read the information here for this error message: https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/ but this is specific to merges and I don't know how to apply it to the issue with this R script.

 

I am aware that the R script references the query; I wanted to use R code to transform a column in a query that I had already imported and transformed the data for rather than running all of the import and transformation steps in R (in this case I would have used Get Data -> R Script instead).

 

How do I get this query to work with the R code transform step in it?

Need Help

8 Replies

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi RMel , 

    You could try below Code to see whether it work or not (ds[[1]] is the first column in dataset), this works well when in single dataset 

     

    # 'dataset' holds the input data for this script


ds <- dataset

ds$progress <- ds[[1]]*100

dataset <- ds

     

     By the way, I have other error information(can't convert to number when I re-produce your design), I am not sure whether this is caused by query combine or others, and I will test this in my environment, I will inform you as soon as possible. 

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • RMel's avatar
      RMel
      Frequent Visitor
      6 years ago

      Hi dax,

      I tried this but unfortunately I am still getting the same error!

      Thanks!

      • mahoneypat's avatar
        mahoneypat
        Microsoft Employee
        6 years ago

        You need to return a dataframe back to the query.  Does this work?

         

        dataset$double_revenue <- dataset$Revenue*2

        dataset

         

        If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

        Regards,

        Pat

  • Carmichael's avatar
    Carmichael
    Advocate III
    5 years ago

    RMel I was having the same problem referenced here and couldnt for the life of me figure it out.  Found the following post which highlighted the solution (at least for me).  

     

    By changing this setting on the file to ignore privacy levels it worked for me.  I am sure there are ramifications of publishing models with this option unchecked but for now I can at leaset continue with using R to perform necessary transformations on existing queries.

    https://community.powerbi.com/t5/Desktop/Formula-Firewall-Query-references-other-queries-or-steps/td-p/150725