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

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,statecode"), 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.

Duplicate key quote entity Dynamics CRM | Microsoft Fabric Community

5 Replies

v-huizhn-msft
Microsoft Employee
9 years ago
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
  Frequent Visitor
  9 years ago
  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
    Microsoft Employee
    9 years ago
    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
Frequent Visitor
9 years ago
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,statecode"),
    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.

Forum Discussion

Duplicate key quote entity Dynamics CRM

5 Replies

Related content

Duplicate ranking

Count distinct duplicate entries across entity

Dynamic Columns duplicating

remove duplicate value within a row

Removing Duplicates