none
Problem with Uri.Parts RRS feed

  • Question

  • Hello Guys:)

    I made a complicated query. It works inside power query, but when I'm trying to load into excel - it throws exception. 

    I deleted all the steps from the end and found that problem is in Uri.Parts. 

    I've tried to handle it with construction 

    = Table.AddColumn(#"Changed Type1", "Uri", each try Uri.Parts([URL без utm]) otherwise [])

    It doesn't work. 

    After I tried  

    = Table.RemoveRowsWithErrors(#"Added Custom", {"Uri"}) 

    and it doesn't work too. 

    How can I handle this problem? 


    =============

    Error Message:

    Value cannot be null.
    Parameter name: key

    Stack Trace:

    Microsoft.Mashup.Evaluator.Interface.ErrorException: Value cannot be null.
    Parameter name: key ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Value cannot be null.
    Parameter name: key ---> System.ArgumentNullException: Value cannot be null.
    Parameter name: key
       at System.Collections.Generic.Dictionary`2.FindEntry(TKey key)
       at Microsoft.Mashup.Engine1.Runtime.Keys.DictionaryStringKeys..ctor(String[] keys)
       at Microsoft.Mashup.Engine1.Runtime.Keys.New(String[] keys)
       at Microsoft.Mashup.Engine1.Library.Uris.UriHelper.CreateQueryRecord(String query)
       at Microsoft.Mashup.Engine1.Library.Uris.UriModule.PartsFunctionValue.TypedInvoke(TextValue uri)
       at Microsoft.Mashup.Engine1.Runtime.NativeFunctionValue1`2.Invoke(Value arg0)
       at Microsoft.Mashup.Engine1.Language.DebugExpression.Execute(Value frame)
       at Microsoft.Mashup.Engine1.Runtime.TransformValueReference.get_Value()
       at Microsoft.Mashup.Engine1.Runtime.OptionalFieldAccessValueReference.GetValue()
       at Microsoft.Mashup.Engine1.Runtime.FieldAccessValueReference.get_Value()
       at Microsoft.Mashup.Engine1.Runtime.RecordValue.ReferenceArrayRecordValue.get_Item(Int32 index)
       at Microsoft.Mashup.Engine1.Library.Table.TableModule.Table.ErrorSelectingTableValue.ErrorFilteringEnumerator.MoveNext()
       at Microsoft.Mashup.Engine1.Library.Table.TableModule.Table.AddIndexColumnFunctionValue.AddIndexColumnTableValue.AddIndexColumnEnumerator.MoveNext()
       at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
       at Microsoft.Internal.IEnumerableExtensions.MaxK[T](IEnumerable`1 list, Int32 count, IComparer`1 comparer)
       at Microsoft.Mashup.Engine1.Language.Query.SortQuery.SortEnumerable.Sort(IEnumerable`1 values, IComparer`1 comparer, Int64 takeCount)
       at Microsoft.Mashup.Engine1.Language.Query.SortQuery.SortEnumerable.GetEnumerator()
       at Microsoft.Mashup.Engine1.Language.Query.QueryTableValue.GetEnumerator()
       at Microsoft.Mashup.Engine1.Runtime.TableValue.Microsoft.Mashup.Engine.Interface.ITableValue.GetEnumerator()
       at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.ValuePreviewValueSource.TableValuePreviewValueSource.SerializeRows(Int32 count)
       at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.ValuePreviewValueSource.TableValuePreviewValueSource.get_SmallValue()
       at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.<>c__DisplayClass11.<OnBeginGetPreviewValueSource>b__10(EvaluationResult2`1 result)
       at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetResult[T](IMessageChannel channel, BeginGetResultMessage message, Action`1 handler)
       --- End of inner exception stack trace ---
       at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetResult[T](IMessageChannel channel, BeginGetResultMessage message, Action`1 handler)
       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(String[] args)
       at Microsoft.Mashup.Container.EvaluationContainerMain.Main(String[] args)
       --- End of inner exception stack trace ---
       at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.OnEvaluationException(IMessageChannel channel, EvaluationExceptionMessage 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.Interface.IMessageChannelExtensions.WaitFor[T](IMessageChannel channel)
       at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.PreviewValueSourceRemoteEvaluation.PreviewValueSource.<>c__DisplayClass7.<WaitForSerializedValue>b__6()
       at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.RemoteEvaluation.TranslateCancelExceptions(Action action)
       at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.PreviewValueSourceRemoteEvaluation.PreviewValueSource.get_SmallValue()
       at Microsoft.Mashup.Host.Document.Analysis.PackageDocumentAnalysisInfo.PackagePartitionAnalysisInfo.SetPreviewValue(EvaluationResult2`1 result, Func`1 getStaleSince, Func`1 getSampled)

    Invocation Stack Trace:

       at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
       at Microsoft.Mashup.Client.ClientShared.UnexpectedExceptionHandler.HandleException(Exception e)
       at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
       at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
       at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
       at System.Delegate.DynamicInvokeImpl(Object[] args)
       at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
       at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
       at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ThreadContext.LocalModalMessageLoop(Form form)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
       at Microsoft.Mashup.Client.ClientShared.WindowManager.ShowDialog[T](T form, IWin32Window owner)
       at Microsoft.Mashup.Client.Excel.EditorManager.ShowModalEditor(IWin32Window ownerWindow, IWorkbookIdentity workbookIdentity, EditorDialogParameters editorDialogParameters, Queries queries)
       at Microsoft.Mashup.Client.Excel.DialogManager.TryShowModalEditor(IWin32Window ownerWindow, IWorkbook workbook, Query query, Boolean isNewQuery, Boolean navigatorExpanded)
       at Microsoft.Mashup.Client.Excel.ExcelQueryServices.<>c__DisplayClass1d.<EditQuery>b__1c(IWorkbook workbook)
       at Microsoft.Mashup.Client.Excel.ExcelQueryServices.TryInvokeOnWorkbook(Action`1 action)
       at Microsoft.Mashup.Client.Excel.QueriesTaskPaneControl.<>c__DisplayClass6.<EditQuery>b__5()
       at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
       at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
       at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
       at System.Delegate.DynamicInvokeImpl(Object[] args)
       at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
       at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
       at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)


    Monday, July 20, 2015 9:07 PM

Answers

  • Well,  i made my own workaround just to find "&" without following "=" symbol

        #"Added Custom1" = Table.AddColumn(Custom1, "Split1", each Text.Split([URL.2],"&")),
        #"Added Custom2" = Table.AddColumn(#"Added Custom1", "listWithEmpty", each List.RemoveNulls(List.Transform ([Split1], each if Text.Contains(_, "=") then _ else null))),
        #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom", each Text.Combine([listWithEmpty],"&")),

    It looks like this. 

    As I always concerned in terms of code performance, I wonder if the way of Using Table.TransformColumn more efficient than Table.AddColumn + Table.RemoveColumns? 
    Curt, what do you think? 

    Tuesday, July 21, 2015 10:17 PM

All replies

  • Well, I've found what makes it. Url with "&" without following "=". 

    http://www.ya.ru/?context=search&text=сумка-чехол_для_автомобильного_сидения_mommy_&_me&sort=bests

    Now I'm trying to figure out how to check all the urls with such a mistake without regex or wildcards


    Monday, July 20, 2015 11:00 PM
  • Thanks for reporting this problem! I've filed a bug. Unfortunately, there's no workaround for this problem :(.
    Tuesday, July 21, 2015 3:47 PM
  • Well,  i made my own workaround just to find "&" without following "=" symbol

        #"Added Custom1" = Table.AddColumn(Custom1, "Split1", each Text.Split([URL.2],"&")),
        #"Added Custom2" = Table.AddColumn(#"Added Custom1", "listWithEmpty", each List.RemoveNulls(List.Transform ([Split1], each if Text.Contains(_, "=") then _ else null))),
        #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom", each Text.Combine([listWithEmpty],"&")),

    It looks like this. 

    As I always concerned in terms of code performance, I wonder if the way of Using Table.TransformColumn more efficient than Table.AddColumn + Table.RemoveColumns? 
    Curt, what do you think? 

    Tuesday, July 21, 2015 10:17 PM
  • The underlying code between the two is basically the same.
    Wednesday, July 22, 2015 2:15 AM