Value cannot be null power bi query

[Anonymous]

Hi All, Need help

I am running a simple query to extract the data from the rest API based on the date query variable. Below is my query

let
dtquery=Text.From(#date(2020,7,7)),
Source=Json.Document(Web.Contents("https://serviceinstance.xxxx.com/api/now/table/incident?&sysparm_display_value=true&sysparm_exclude_...", [Query=[dt=dtquery]]
)),

#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"

when trying to run I am getting this error:

Unexpected error: Value cannot be null.
Parameter name: str
Details:
Microsoft.Mashup.Evaluator.Interface.ErrorException: Value cannot be null.
Parameter name: str ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Value cannot be null.
Parameter name: str ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Value cannot be null.
Parameter name: str ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Value cannot be null.
Parameter name: str ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Value cannot be null.
Parameter name: str ---> System.ArgumentNullException: Value cannot be null.
Parameter name: str ---> System.ArgumentNullException: Value cannot be null.
Parameter name: str
at Microsoft.Mashup.Common.UriMethods.EscapeDataString(String str, Boolean legacyBehavior)
at Microsoft.Mashup.Engine1.Library.Uris.UriHelper.BuildQueryString(NameValueCollection queryParameters)
at Microsoft.Mashup.Engine1.Library.Uris.UriHelper.AddQueryRecord(UriBuilder uriBuilder, NameValueCollection newQueryString)
at Microsoft.Mashup.Engine1.Library.Uris.UriHelper.AddQueryRecord(Uri originalUri, NameValueCollection queryParameters)
at Microsoft.Mashup.Engine1.Library.Uris.UriHelper.AddQueryRecord(Uri originalUri, RecordValue newQuery)
at Microsoft.Mashup.Engine1.Library.Web.Request.Create(IEngineHost host, String resourceKind, String resourcePath, TextValue url, Value query, Value content, String webApiKey, Value headers, Value timeout, Int32[] nonErrors, Value relativePath, Value credentialQuery, Action`2 responseErrorHandler)
at Microsoft.Mashup.Engine1.Library.Web.WebOptionsHelper.GetRequest(IEngineHost host, String resourceKind, String resourcePath, TextValue url, Value options, Boolean privilegedMode, Action`2 responseErrorHandler)
at Microsoft.Mashup.Engine1.Library.Web.WebModule.WebContentsFunctionValue.TypedInvoke(TextValue url, Value options)
at Microsoft.Mashup.Engine1.Runtime.NativeFunctionValue2`3.Invoke(Value arg0, Value arg1)
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.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.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.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.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.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.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.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.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.Language.MembersFunctionValue0.Invoke()
at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.BeginGetResult(DocumentEvaluationParameters parameters, Action`1 callback)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.BeginGetResult(DocumentEvaluationParameters parameters, Action`1 callback)
at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.BeginGetResult(DocumentEvaluationParameters parameters, Action`1 callback)
at Microsoft.Mashup.Evaluator.Interface.IDocumentEvaluatorExtensions.GetResult[T](IDocumentEvaluator`1 evaluator, DocumentEvaluationParameters parameters)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetResult[T](IMessageChannel channel, BeginGetResultMessage message, Action`1 action)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetPreviewValueSource(IMessageChannel channel, BeginGetPreviewValueSourceMessage message)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.OnMessageWithUnknownChannel(IMessageChannel baseChannel, MessageWithUnknownChannel messageWithUnknownChannel)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.EvaluationHost.Run()
at Microsoft.Mashup.Container.EvaluationContainerMain.Run(Object args)
at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass9_0.<CreateAction>b__0(Object o)
at Microsoft.Mashup.Container.EvaluationContainerMain.SafeRun(String[] args)
at Microsoft.Mashup.Container.EvaluationContainerMain.Main(String[] args)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.<>c__DisplayClass3_0.<BeginGetResult>b__0(EvaluationResult2`1 result)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.<>c__DisplayClass3_0.<BeginGetResult>b__0(EvaluationResult2`1 result)
at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.BeginGetResult(DocumentEvaluationParameters parameters, Action`1 callback)
at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.BeginGetResult(DocumentEvaluationParameters parameters, Action`1 callback)
at Microsoft.Mashup.Evaluator.Interface.IDocumentEvaluatorExtensions.GetResult[T](IDocumentEvaluator`1 evaluator, DocumentEvaluationParameters parameters)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetResult[T](IMessageChannel channel, BeginGetResultMessage message, Action`1 action)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetPreviewValueSource(IMessageChannel channel, BeginGetPreviewValueSourceMessage message)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.OnMessageWithUnknownChannel(IMessageChannel baseChannel, MessageWithUnknownChannel messageWithUnknownChannel)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.EvaluationHost.Run()
at Microsoft.Mashup.Container.EvaluationContainerMain.Run(Object args)
at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass9_0.<CreateAction>b__0(Object o)
at Microsoft.Mashup.Container.EvaluationContainerMain.SafeRun(String[] args)
at Microsoft.Mashup.Container.EvaluationContainerMain.Main(String[] args)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.<>c__DisplayClass9_1.<OnBeginGetPreviewValueSource>b__1()
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.<>c__DisplayClass0_0.<RunStub>b__0()
at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.EvaluationHost.<>c__DisplayClass11_0.<TryReportException>b__1()
at Microsoft.Mashup.Common.SafeExceptions.IgnoreSafeExceptions(IEngineHost host, IHostTrace trace, Action action)
at Microsoft.Mashup.Evaluator.EvaluationHost.TryReportException(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Exception exception)
at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.RunStub(IEngineHost engineHost, IMessageChannel channel, Func`1 getPreviewValueSource)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.<>c__DisplayClass12_1`1.<OnBeginGetResult>b__0()
at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetResult[T](IMessageChannel channel, BeginGetResultMessage message, Action`1 action)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetPreviewValueSource(IMessageChannel channel, BeginGetPreviewValueSourceMessage message)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.OnMessageWithUnknownChannel(IMessageChannel baseChannel, MessageWithUnknownChannel messageWithUnknownChannel)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.EvaluationHost.Run()
at Microsoft.Mashup.Container.EvaluationContainerMain.Run(Object args)
at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass9_0.<CreateAction>b__0(Object o)
at Microsoft.Mashup.Container.EvaluationContainerMain.SafeRun(String[] args)
at Microsoft.Mashup.Container.EvaluationContainerMain.Main(String[] args)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.EvaluationHost.OnException(IEngineHost engineHost, IMessageChannel channel, ExceptionMessage message)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.Interface.IMessageChannelExtensions.WaitFor[T](IMessageChannel channel)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.get_TableSource()
at Microsoft.Mashup.Evaluator.Interface.TracingPreviewValueSource.get_TableSource()
at Microsoft.Mashup.Host.Document.Analysis.PackageDocumentAnalysisInfo.PackagePartitionAnalysisInfo.SetPreviewValue(EvaluationResult2`1 result, Func`1 getStaleSince, Func`1 getSampled)

[v-eachen-msft]

Hi @Anonymous ,

Here is a detailed blog about Web.Contents() for your reference:

https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-in-power-query-and-power-bi-m-code/

[Anonymous]

I tryed this and there is no error :

the problem is somewhere else

[Anonymous]

the main suspect seems to be the second null of this expression:

#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

you could try .

1)  remove all optional parameters then use  #"Converted to Table" = Table.FromList(Source) and see ...

2) should set the second null to some value.  I dont know the role and the scope of each parameter of this function and the MS docs don't help very much on this aspect, but we (i.e. you) can experiment ...

[Smauro]

@Anonymous's issue must be with:

Web.Contents("https://serviceinstance.xxxx.com/api/now/table/incident?&sysparm_display_value=true&sysparm_exclude_...", [Query=[dt=dtquery]])

when it actually runs, it probably gets transformed to:

Web.Contents("https://serviceinstance.xxxx.com/api/now/table/incident?&dt=dtquery")

try adding all queries in the [Query=[]] record

[Anonymous]

@Smauro Thanks for the reply.

I have a query parameter in my rest API which filters data based on date which is like "sysparm_query=sys_created_on>%3Djavascript%3Ags.dateGenerate(%272020-07-07%27%2C%2700%3A00%3A00%27)"

as you can see its uses a java script for date format and also formats the operators like greater than or equal to for processing.

how can i pass this value in my web content as parameter?