Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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
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
The problem just persists with the quote entity. I can successfully add other CRM entities and also Excel queries.
Regards,
Thomas
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |