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?
