Duplicate key quote entity Dynamics CRM

[th_ge]

Hi

In my report I added a OData data source which connects an on premise Dynamics CRM. When I try to add the quote entity as a query there is the following error:

I guess that the problem is the column “quotenumber” which is an out of the box field used for autonumbering. There are in fact duplicate records in the system with the same qutonumber. This behavior is normal, because if you mark a quote as lost, there is an option to create a new one which actually inherits the quotenumber from its predecessor. I also checked records for duplicate guids which is not the case.

Does anybody have an idea to resolve this issue?

Thanks and regards,

Thomas

[th_ge · Accepted solution]

The solution for the problem is to add filter and select statements directly in the OData request. When you select the quote entity in the user interface, the M expression in the advanced editor looks like this:

let
    Source = OData.Feed("https://<SERVERURL>/<ORGANIZATION>/api/data/v8.0/"),
    quotes_table = Source{[Name="quotes",Signature="table"]}[Data]   
in
    quotes_table

The following M expression shows how to filter the OData query directly:

let   
    Source = OData.Feed("https://<SERVERURL>/<ORGANIZATION>/api/data/v8.0/quotes?$select=quoteid,createdon,_createdby_value,s..."),
    quotes_table = Table.SelectColumns(Source,{"quoteid","_createdby_value","statecode"})
in
    quotes_table

With this procedure you simply select the columns you really need and therefore we can avoid the error with duplicate keys.

[v-huizhn-msft]

Hi @th_ge,

You get the error message when load the resource data, or after you have operated other steps? Could you please share more details for further analysis?

Best Regards,
Angelia

[th_ge]

Hi Angelia (@v-huizhn-msft)

Thanks' for your help!

No, the error appears as soon as I select the table. It also shows up in the preview of the dataset navigator:

I also already tired the latest version of Power BI which didn't help. Below you find the complete error messages:

Feedback Type:
Frown (Error)

Timestamp:
2017-03-06T11:47:24.8410805Z

Local Time:
2017-03-06T12:47:24.8410805+01:00

Product Version:
2.40.4554.463 (PBIDesktop) (x64)

Release:
October, 2016

IE Version:
11.0.9600.18537

OS Version:
Microsoft Windows NT 6.1.7601 Service Pack 1 (x64 de-DE)

CLR Version:
4.6.1 or later [Release Number = 394271]

Workbook Package Info:
1* - de-CH, fastCombine: Disabled.

Peak Working Set:
479 MB

Private Memory:
435 MB

Peak Virtual Memory:
3.73 GB

Error Message:
An item with the same key has already been added.

Stack Trace:
Microsoft.Mashup.Evaluator.Interface.ErrorException: An item with the same key has already been added. ---> System.ArgumentException: An item with the same key has already been added. ---> System.ArgumentException: An item with the same key has already been added.
   at System.ThrowHelper.ThrowArgumentException(ExceptionResource resource)
   at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)
   at Microsoft.Mashup.Engine1.Library.OData.ODataStructuralValueConverter.CreateEntryRecordWithStructuralProperties(TypeValue type, IEnumerable`1 properties, Dictionary`2 links, Keys& keys, Dictionary`2& props)
   at Microsoft.Mashup.Engine1.Library.OData.ODataStructuralValueConverter.CreateEntryValue[TODataException](ODataEnvironmentBase environment, TypeValue type, IEnumerable`1 properties, Dictionary`2 links, Func`4 createEntryLinkValueReference, Func`2 marshalFromClr, ODataEntry entry, IEdmEntitySetBase entryEntitySet)
   at Microsoft.Mashup.Engine1.Library.OData.V4.ODataReaderEnumerator.CreateEntryValue(TypeValue type, ODataEntry entry, IEdmNavigationSource entryEntitySet, Dictionary`2 links)
   at Microsoft.Mashup.Engine1.Library.OData.V4.ODataReaderEnumerator.MoveNext()
   at Microsoft.Mashup.Engine1.Library.OData.V4.BatchEnumerator.ReadPage()
   at Microsoft.Mashup.Engine1.Library.OData.V4.BatchEnumerator.MoveNext()
   at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.ValuePreviewValueSource.TableValuePreviewValueSource.SerializeRows(Int32 count)
   at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.ValuePreviewValueSource.TableValuePreviewValueSource.get_SmallValue()
   at Microsoft.Mashup.Evaluator.Interface.TracingPreviewValueSource.get_SmallValue()
   at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.<>c__DisplayClass5.<RunStub>b__3()
   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__DisplayClass7.<TryReportException>b__6()
   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__DisplayClass1f`1.<OnBeginGetResult>b__1c()
   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.Evaluator.SafeThread2.<>c__DisplayClass15.<CreateAction>b__14(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_SmallValue()
   at Microsoft.Mashup.Evaluator.Interface.TracingPreviewValueSource.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.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace)
   at Microsoft.Mashup.Client.ClientShared.FeedbackErrorInfo..ctor(String message, Nullable`1 errorCode, String requestId, Exception exception)
   at Microsoft.Mashup.Client.ClientShared.UnexpectedExceptionHandler.<>c__DisplayClass1.<HandleException>b__0()
   at Microsoft.Mashup.Client.ClientShared.UnexpectedExceptionHandler.HandleException(Exception e)
   at Microsoft.Mashup.Client.ClientShared.Ux.HtmlForm.<>c__DisplayClass6.<OnAjaxException>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)
   at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
   at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
   at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
   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.ClientShared.Ux.FloatingDialog.NavigatorFloatingDialog.ShowEditLoad(IWin32Window owner, WindowsHost windowsHost, Query query, ITelemetryService telemetryService)
   at Microsoft.Mashup.Client.ClientShared.DataImporter.ShowNavigatorOnSourceQuery(Query query, IEnumerable`1& importedQueries)
   at Microsoft.Mashup.Client.ClientShared.DataImporter.ImportNavigationSource(Query query)
   at Microsoft.Mashup.Client.ClientShared.DataImporter.OnGetPreviewResult(PreviewResult preview, Query query, String sourceID, String formulaTitle, Nullable`1 explicitImportDestination, Boolean isNewQuery, Boolean isFromEditor)
   at Microsoft.Mashup.Client.ClientShared.DataImporter.<>c__DisplayClass17.<OnQuerySettingsResolved>b__14()
   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.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
   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.PowerBI.Client.Program.<>c__DisplayClassb.<Main>b__0()
   at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
   at Microsoft.PowerBI.Client.Program.Main(String[] args)

User ID:
a4e923a6-a07d-4f7c-911f-965cec3be126

Session ID:
c9306608-59b3-4fe1-bdc3-1a0c6f6c2fb7

Telemetry Enabled:
True

Model Default Mode:
Import

Snapshot Trace Logs:
C:\Users\iso_get\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShot715656936.zip

Performance Trace Logs:
C:\Users\iso_get\AppData\Local\Microsoft\Power BI Desktop\PerformanceTraces.zip

Disabled Preview Features:
PBI_RedShift
PBI_Impala
PBI_Snowflake
PBI_shapeMapVisualEnabled
PBI_allowBiDiCrossFilterInDirectQuery
PBI_forecastEnabled
PBI_esriEnabled
PBI_mobileReportAuthoringEnabled
PBI_snapToDotGrid

Disabled DirectQuery Options:
DirectQuery_Unrestricted

Cloud:
GlobalCloud

DPI Scale:
100%

Formulas:

section Section1;

shared #"NavigatorBase_feac0c85-5603-4567-9af9-5bdc4c353a8e" = let
    Source = OData.Feed("<CRMURL>/<CRMORG>/api/data/v8.0/")
in
    Source;

Regards,

Thomas

[v-huizhn-msft]

Hi @th_ge,

Please change your resource table to Excel table. If you can load excel table correctly. Please check your connection. More details, please refer to this article.

Best Regards,
Angelia

[th_ge]

Hi @v-huizhn-msft

The problem just persists with the quote entity. I can successfully add other CRM entities and also Excel queries.

Regards,

Thomas