PBI Desktop Error Message: An item with the same key has already been added. Using OData.Feed

[DaveTheOx]

I have OData V4 metadata with the following annotations:

<Annotation Term="Org.OData.Capabilities.V1.NavigationRestrictions">
<Record>
<PropertyValue Property="Navigability">
<EnumMember>Org.OData.Capabilities.V1.NavigationType/Single</EnumMember>
</PropertyValue>
<PropertyValue Property="RestrictedProperties">
<Collection>
<Record>
<PropertyValue Property="NavigationProperty" NavigationPropertyPath="Parent"/>
<PropertyValue Property="Navigability">
<EnumMember>Org.OData.Capabilities.V1.NavigationType/Recursive</EnumMember>
</PropertyValue>
</Record>
<Record>
<PropertyValue Property="NavigationProperty" NavigationPropertyPath="Descendant"/>
<PropertyValue Property="Navigability">
<EnumMember>Org.OData.Capabilities.V1.NavigationType/Recursive</EnumMember>
</PropertyValue>
</Record>
<Record>
<PropertyValue Property="NavigationProperty" NavigationPropertyPath="Items/Parent"/>
<PropertyValue Property="Navigability">
<EnumMember>Org.OData.Capabilities.V1.NavigationType/Recursive</EnumMember>
</PropertyValue>
</Record>
<Record>
<PropertyValue Property="NavigationProperty" NavigationPropertyPath="Items/Descendants"/>
<PropertyValue Property="Navigability">
<EnumMember>Org.OData.Capabilities.V1.NavigationType/Recursive</EnumMember>
</PropertyValue>
</Record>
</Collection>
</PropertyValue>
</Record>
</Annotation>

If I use OData.Feed to access my OData API, I get an exception in PBI Desktop when reading the $metadata.

Feedback Type:
Frown (Error)

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. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: An item with the same key has already been added. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: An item with the same key has already been added. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: An item with the same key has already been added. ---> 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.V4.AnnotationProcessor.GetRestrictedNavigationProperties(IEdmRecordExpression recordExpressionValue)
at Microsoft.Mashup.Engine1.Library.OData.V4.AnnotationProcessor.BuildNavigationRestrictions(Capabilities capability, IEdmExpression expressionValue)
at Microsoft.Mashup.Engine1.Library.OData.V4.AnnotationProcessor.ProcessCapabilities(String displayName, IEdmModel model, IEdmVocabularyAnnotatable annotatable, Annotations annotations, ODataUserSettings userSettings)
at Microsoft.Mashup.Engine1.Library.OData.V4.EdmModelProcessor.ProcessAndAddCapability(String key, IEdmVocabularyAnnotatable annotatable)
at Microsoft.Mashup.Engine1.Library.OData.V4.EdmModelProcessor.ProcessEntityContainers()
at Microsoft.Mashup.Engine1.Library.OData.EdmModelProcessorBase`1.Build(HttpResource resource)
at Microsoft.Mashup.Engine1.Library.OData.V4.ODataEnvironment.Create(ServiceDocumentWrapper serviceDoc, Uri metadataUri, Value headers, HttpResource resource, Uri requestUri, ResourceCredentialCollection credentials, IEngineHost host, ODataSettings settings, ODataUserSettings userSettings, Boolean useCachedCredentials)
at Microsoft.Mashup.Engine1.Library.OData.V4.ODataResponse.Create(Uri requestUri, HttpResponseData responseData, HttpResource resource, Value headers, ResourceCredentialCollection credentials, IEngineHost host, ODataSettings settings, ODataUserSettings userSettings)
at Microsoft.Mashup.Engine1.Library.OData.ODataModule.GetFeed(HttpResource resource, TextValue serviceUriValue, TextValue uriValue, Value headers, IEngineHost host, ResourceCredentialCollection credentials, ODataSettingsBase settings, ODataUserSettings userSettings, Boolean useCachedCredentials)
at Microsoft.Mashup.Engine1.Library.OData.ODataModule.FeedFunctionValue.TypedInvoke(TextValue serviceUri, Value headers, Value options)
at Microsoft.Mashup.Engine1.Runtime.NativeFunctionValue3`4.Invoke(Value arg0, Value arg1, Value arg2)
...

--- 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.FirewallPartition.BeginGetResult[T](Action`1 callback)
at Microsoft.Mashup.Evaluator.FirewallDocumentEvaluator.Evaluation`1.OnBufferComplete(Exception exception)
at Microsoft.Mashup.Evaluator.Firewall.BeginBufferPartitions(Action`1 callback)
at Microsoft.Mashup.Evaluator.FirewallDocumentEvaluator.BeginGetResultInternal[T](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)
...

--- 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)
...

--- 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)

...

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

Invocation Stack Trace:
at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
at Microsoft.Mashup.Client.UI.Shared.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace, String exceptionMessage)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.GetStackTraceInfo(Exception e)
...

at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at Microsoft.PowerBI.Client.Program.Main(String[] args)

Model Default Mode:
Empty

Snapshot Trace Logs:
C:\Users\dlbrann\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShot1445884809.zip

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

Enabled Preview Features:
PBI_newFromWeb
CustomConnectors

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_variationUIChange
PBI_canvasTooltips
PBI_PythonSupportEnabled
PBI_showIncrementalRefreshPolicy
PBI_compositeModels
PBI_DB2DQ

Disabled DirectQuery Options:
PBI_DirectQuery_Unrestricted
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

Formulas:

section Section1;

shared Pipes = let
Source = SmartApiOData.Feed("https://samtest.spclouddave.com/SampleService/Sppid/V2", null),
Pipes_table = Source{[Name="Pipes",Signature="table"]}[Data]
in
Pipes_table;

[DaveTheOx]

@v-qiuyu-msft, your response:
"Therefore, to get PBI to work, the customer may as well remove those navigation restrictions entirely; "

is not a viable solution.   Our OData API is consumed by many clients and Power BI Desktop is just one of many.   We cannot alter our metadata to work around a bug in Power BI.  

Thanks,
Dave

[v-qiuyu-msft]

Hi @DaveTheOx,

I have delivered your concern internally, will update here once I get any information. 

Best Regards,
Qiuyun Yu 

[v-qiuyu-msft]

Hi @DaveTheOx,

I got information that "We will fix this, but the fix will not be shipped until the November release. ". 

Best Regards,
Qiuyun Yu 

[DaveTheOx]

Thanks @v-qiuyu-msft!   That's great news.

[v-qiuyu-msft]

Hi @DaveTheOx, 

please download the Power BI desktop November version 2.64.5285.741 and test is the issue is resolved. 

Best Regards,
Qiuyun Yu 

[DaveTheOx]

@v-qiuyu-msft, unfortunately the bug is still there.  

Feedback Type:
Frown (Error)

Timestamp:
2018-11-28T21:09:12.7188717Z

Local Time:
2018-11-28T15:09:12.7188717-06:00

Session ID:
8b122f94-2e36-43a6-8151-2f48887f2a16

Release:
November 2018

Product Version:
2.64.5285.741 (18.11) (x64)

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. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: An item with the same key has already been added. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: An item with the same key has already been added. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: An item with the same key has already been added. ---> 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.V4.AnnotationProcessor.GetRestrictedNavigationProperties(IEdmRecordExpression recordExpressionValue)
at Microsoft.Mashup.Engine1.Library.OData.V4.AnnotationProcessor.BuildNavigationRestrictions(Capabilities capability, IEdmExpression expressionValue)
at Microsoft.Mashup.Engine1.Library.OData.V4.AnnotationProcessor.ProcessCapabilities(String displayName, IEdmModel model, IEdmVocabularyAnnotatable annotatable, Annotations annotations, ODataUserSettings userSettings)
at Microsoft.Mashup.Engine1.Library.OData.V4.EdmModelProcessor.ProcessAndAddCapability(String key, IEdmVocabularyAnnotatable annotatable)
at Microsoft.Mashup.Engine1.Library.OData.V4.EdmModelProcessor.ProcessEntityContainers()
at Microsoft.Mashup.Engine1.Library.OData.EdmModelProcessorBase`1.Build(HttpResource resource)
at Microsoft.Mashup.Engine1.Library.OData.V4.ODataEnvironment.Create(ServiceDocumentWrapper serviceDoc, Uri metadataUri, Value headers, HttpResource resource, Uri requestUri, ResourceCredentialCollection credentials, IEngineHost host, ODataSettings settings, ODataUserSettings userSettings, Boolean useCachedCredentials)
........
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)

Invocation Stack Trace:
at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
at Microsoft.Mashup.Client.UI.Shared.DataImporter.HandleImportEvaluationException(ExceptionResult exceptionView, Query query, String sourceID, String formulaTitle, Boolean isNewQuery, Boolean isFromEditor)
at Microsoft.Mashup.Client.UI.Shared.DataImporter.OnGetPreviewResult(PreviewResult preview, Query query, String sourceID, String formulaTitle, Nullable`1 explicitImportDestination, Boolean isNewQuery, Boolean isFromEditor)
at Microsoft.Mashup.Client.UI.Shared.DataImporter.GetPreviewResult(Query query, String sourceID, String formulaTitle, Boolean isNewQuery, Boolean isFromEditor, Nullable`1 explicitImportDestination)
at Microsoft.Mashup.Client.UI.Shared.DataImporter.<>c__DisplayClass20.<OnQuerySettingsResolved>b__1d()
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.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.UI.Shared.WebDialogs.WebDialog.<>n__FabricatedMethod8(IWindowHandle )
at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
at Microsoft.PowerBI.Client.Program.<>c__DisplayClass10.<Main>b__0()
at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<>c__DisplayClass7.<HandleExceptionsWithNestedTasks>b__6()
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at Microsoft.PowerBI.Client.Program.Main(String[] args)

OS Version:
Microsoft Windows NT 10.0.17134.0 (x64 en-US)

CLR Version:
4.7 or later [Release Number = 461808]

Peak Virtual Memory:
38.2 GB

Private Memory:
394 MB

Peak Working Set:
520 MB

IE Version:
11.407.17134.0

User ID:
5ccbcbe9-561f-48a2-bec6-a504e314553e

Workbook Package Info:
1* - en-US, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: True.

Telemetry Enabled:
True

Model Default Mode:
Empty

Snapshot Trace Logs:
C:\Users\dlbrann\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShot1554275165.zip

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

Enabled Preview Features:
MIntellisense

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_PdfImport
PBI_ColumnProfiling
PBI_variationUIChange
PBI_PythonSupportEnabled
PBI_showIncrementalRefreshPolicy
PBI_showManageAggregations
PBI_FuzzyMatching
PBI_EnableWebDiagramView
PBI_improvedFilterExperience

Disabled DirectQuery Options:
PBI_DirectQuery_Unrestricted
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

DPI Scale:
100%

Supported Services:
Power BI

Formulas:

section Section1;

shared Query1 = let
Source = SmartApiOData.Feed("https://samtest.spclouddave.com/SampleService/Sppid/V2", null)
in
Source;

[DaveTheOx]

Hi @v-qiuyu-msft,

The bug still exists in the latest Power BI Desktop release.   What is the next step?   Do you mark the internal bug report as Fix-Rejected?

Do you need a test case?   I have a public API that demonstrates the problem that the dev team can use for testing.   Just let me know and I can provide access.

Thanks,
Dave

[v-qiuyu-msft]

Hi @DaveTheOx,

I consulted it internally, and got below information: 

You may need to modify your query so that Options = [ODataVersion = 4.0, Implementation = "2.0"]. All new changes to the OData connector are happening on Implementation 2.0; the older version remains only for backwards-compatibility with existing connections. New connections created through the UI should automatically be using the new implementation.

Best Regards,
Qiuyun Yu 

[Anonymous]

Modified my OdataConnector

From Options = [ODataVersion = 4.0, MoreColumns = true]

to [ ODataVersion = 4, MoreColumns = true, Implementation = "2.0" ].

and it worked as charm

Below is sample code snippet of connector

let
source = OData.Feed(
"http://localhost/WebApi/",
null,
[ ODataVersion = 4, MoreColumns = true, Implementation = "2.0" ])
in
source;

Thanks @v-qiuyu-msft