Forum Discussion
How to run an R script transformation on an existing query?
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
Hi mahoneypat,
Unfortunately I am still getting the same error message with this R script too.
Thanks!
My bad. It's been a while. You need to send the resulting dataframe to output
# 'dataset' holds the input data for this script
dataset$newcolumn <- dataset$Number * 2
output <- datasetI did a test query, if you are interested.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWJVnICsozALGcgy1gpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Letter = _t, Number = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Letter", type text}, {"Number", Int64.Type}}),
#"Run R script" = R.Execute("# 'dataset' holds the input data for this script#(lf)dataset$newcolumn <- dataset$Number * 2#(lf)output <- dataset",[dataset=#"Changed Type"]),
#"""output""" = #"Run R script"{[Name="output"]}[Value]
in
#"""output"""If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Hi mahoneypat,
I tried this and still got the same error. I wondered if this is because I merged this query with another one earlier, so I tried it with a different query that I had not merged previously instead.
This time I received a prompt saying "Information is required about data privacy."
I clicked "Ignore Privacy level checks for this file. Ignoring privacy level checks could expose sensitive or confidential data to an unauthorised person." and clicked "Save" (I wouldn't be able to do this for business data due to security issues but tried it for the purposes of testing.)
Then I received a different error message that starts with (I can't add the whole thing as it exceeds 20,000 characters):
Feedback Type:
Frown (Error)Error Message:
Object reference not set to an instance of an object.Stack Trace:
Microsoft.Mashup.Evaluator.Interface.ErrorException: Object reference not set to an instance of an object. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Object reference not set to an instance of an object. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Object reference not set to an instance of an object. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Object reference not set to an instance of an object. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Object reference not set to an instance of an object. ---> System.NullReferenceException: Object reference not set to an instance of an object. ---> System.NullReferenceException: Object reference not set to an instance of an object.
at Microsoft.PowerBI.Radio.RConnection.get_ServerVersion()
at Microsoft.Mashup.Engine1.Library.Common.DelegatingDbConnection.get_ServerVersion()
at Microsoft.Mashup.Engine1.Library.Common.DelegatingDbConnection.get_ServerVersion()
at Microsoft.Mashup.Engine1.Library.Common.DbEnvironment.LoadServerVersion()
at Microsoft.Mashup.Engine1.Library.Common.DbEnvironment.LoadCachedServerVersion()
at Microsoft.Mashup.Engine1.Library.Common.DbEnvironment.CreateConnection()
at Microsoft.Mashup.Engine1.Library.Common.DbValueBuilder.ConnectionReader..ctor(DbEnvironment environment, Func`2 readerWrapper)
at Microsoft.Mashup.Engine1.Library.Common.NativeQueryTableValue.Initialize()
at Microsoft.Mashup.Engine1.Library.Common.NativeQueryTableValue.EnsureInitialized()
at Microsoft.Mashup.Engine1.Library.Common.NativeQueryTableValue.GetEnumerator()
at Microsoft.Mashup.Engine1.Runtime.TableValue.get_Item(Int32 index)
at Microsoft.Mashup.Engine1.Runtime.TableValue.get_Item(Value key)
at Microsoft.Mashup.Engine1.Runtime.Library.List.ElementWithListCheckFunctionValue.Invoke(Value collection, Value key)
at Microsoft.Mashup.Engine1.Language.FunctionInvocationInstruction2.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.DebugInstruction.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.FieldAccessInstruction.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.DebugInstruction.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.InstructionInvocationInstruction1.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.DebugInstruction.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Value arg0)
at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.Force(Int32 index)
at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.get_Item(Int32 index)
at Microsoft.Mashup.Engine1.Runtime.RecordValue.get_Item(String field)
at Microsoft.Mashup.Engine1.Language.FieldAccessInstruction.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.InstructionInvocationInstruction1.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.DebugInstruction.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.Instruction.Execute(MembersFrame1& frame, Instruction[] instructions)
at Microsoft.Mashup.Engine1.Language.InstructionInvocationInstruction.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.DebugInstruction.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Value arg0)
at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.Force(Int32 index)
at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.get_Item(Int32 index)
at Microsoft.Mashup.Engine1.Runtime.RecordValue.get_Item(String field)
at Microsoft.Mashup.Engine1.Language.FieldAccessInstruction.Execute(Value frame)
at Microsoft.Mashup.Engine1.Language.IfInstruction.Execute(Value frame)
at Microsoft.Mashup.Engine1.Language.DebugInstruction.Execute(Value frame)
at Microsoft.Mashup.Engine1.Language.RuntimeFunctionValue1.Invoke(Value arg0)
at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.Force(Int32 index)
at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.get_Item(Int32 index)
at Microsoft.Mashup.Engine1.Runtime.RecordValue.get_Item(String field)
at Microsoft.Mashup.Engine1.Language.FieldAccessInstruction.Execute(MembersFrame2& frame)
at Microsoft.Mashup.Engine1.Language.MembersFunctionValue2.Invoke(Value arg0, Value arg1)
at Microsoft.Mashup.Engine1.Language.InstructionInvocationInstruction2.Execute(MembersFrame2& frame)
at Microsoft.Mashup.Engine1.Language.FunctionInvocationInstruction2.Execute(MembersFrame2& frame)
at Microsoft.Mashup.Engine1.Language.MembersFunctionValue2.Invoke(Value arg0, Value arg1)
at Microsoft.Mashup.Engine1.Runtime.FunctionValue.MetaTypeFunctionValue.Invoke(Value arg0, Value arg1)
at Microsoft.Mashup.Engine1.Language.InstructionInvocationInstruction2.Execute(MembersFrame2& frame)
at Microsoft.Mashup.Engine1.Language.DebugInstruction.Execute(MembersFrame2& frame)
at Microsoft.Mashup.Engine1.Language.MembersFunctionValue2.Invoke(Value arg0, Value arg1)
at Microsoft.Mashup.Engine1.Language.InstructionInvocationInstruction2.Execute(MembersFrame2& frame)
at Microsoft.Mashup.Engine1.Language.FunctionInvocationInstruction2.Execute(MembersFrame2& frame)
at Microsoft.Mashup.Engine1.Language.MembersFunctionValue2.Invoke(Value arg0, Value arg1)
at Microsoft.Mashup.Engine1.Runtime.FunctionValue.Invoke(Value[] args)
at Microsoft.Mashup.Engine1.Runtime.FunctionValue.MetaTypeFunctionValue.Invoke(Value[] args)
at Microsoft.Mashup.Engine1.Runtime.Extensibility.HostRelinkingFunctionValue.ExtensionFunctionValue.InvokeN(Value[] args)
at Microsoft.Mashup.Engine1.Runtime.NativeFunctionValueN.Invoke(Value[] args)
at Microsoft.Mashup.Engine1.Runtime.NativeFunctionValueN.Invoke(Value arg0, Value arg1)
at Microsoft.Mashup.Engine1.Language.InstructionInvocationInstruction2.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.DebugInstruction.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.FunctionInvocationInstruction2.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.DebugInstruction.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.InstructionInvocationInstruction2.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.DebugInstruction.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Value arg0)
at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.Force(Int32 index)
at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.get_Item(Int32 index)
at Microsoft.Mashup.Engine1.Runtime.RecordValue.get_Item(String field)
at Microsoft.Mashup.Engine1.Language.FieldAccessInstruction.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Value arg0)
at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.Force(Int32 index)
at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.get_Item(Int32 index)
at Microsoft.Mashup.Engine1.Runtime.RecordValue.get_Item(Value key)
at Microsoft.Mashup.Engine1.Runtime.Library.Linker.BindFunctionValue.TypedInvoke(RecordValue environment, Value section, TextValue name)
at Microsoft.Mashup.Engine1.Runtime.NativeFunctionValue3`4.Invoke(Value arg0, Value arg1, Value arg2)
at Microsoft.Mashup.Engine1.Runtime.FunctionValue.Invoke(Value[] args)
at Microsoft.Mashup.Engine1.Language.InstructionInvocationInstruction.Execute(MembersFrame0& frame)
at Microsoft.Mashup.Engine1.Language.MembersFunctionValue0.Invoke()
at Microsoft.Mashup.Engine1.Language.ListInstruction.RuntimeListValue.Force(Int32 index)
at Microsoft.Mashup.Engine1.Language.ListInstruction.RuntimeListValue.get_Item(Int32 index)
at Microsoft.Mashup.Engine1.Runtime.ListValue.get_Item(Value key)
at Microsoft.Mashup.Engine1.Runtime.Library.List.ElementWithListCheckFunctionValue.Invoke(Value collection, Value key)
at Microsoft.Mashup.Engine1.Language.FunctionInvocationInstruction2.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Value arg0)
at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.Force(Int32 index)
at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.get_Item(Int32 index)
at Microsoft.Mashup.Engine1.Runtime.RecordValue.get_Item(String field)
at Microsoft.Mashup.Engine1.Language.FieldAccessInstruction.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.FunctionInvocationInstruction2.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.Instruction.ExecuteCondition(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.DebugInstruction.ExecuteCondition(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.IfInstruction.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.DebugInstruction.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Value arg0)
at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.Force(Int32 index)
at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.get_Item(Int32 index)
at Microsoft.Mashup.Engine1.Runtime.RecordValue.get_Item(String field)
at Microsoft.Mashup.Engine1.Language.FieldAccessInstruction.Execute(MembersFrame0& frame)
at Microsoft.Mashup.Engine1.Language.MembersFunctionValue0.Invoke()
at Microsoft.Mashup.Engine1.Runtime.FunctionValue.Invoke(Value[] args)
at Microsoft.Mashup.Engine1.Engine.Microsoft.Mashup.Engine.Interface.IEngine.Invoke(IValue function, IValue[] arguments)
at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.BeginGetResult(DocumentEvaluationParameters parameters, Action`1 callback)
--- End of inner exception stack trace ---Any ideas as to what this means?
Please see this post.
http://officeusers.blogspot.com/2018/06/using-r-in-power-bi-object-reference.html
Are you using 32 bit R Studio?
Regards
Pat