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]

Also, note I am using the following options for OData.Feed:

oDataOptions = [ODataVersion = 4, MoreColumns = true, IncludeMetadataAnnotations = "*"],
content = OData.Feed(url, headerRecord, oDataOptions)

[v-qiuyu-msft]

Hi @DaveTheOx,

1. From detail error message, I found your PowerQuery to retrieve data is:

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

Based on my research, there is no accessing data function named SmartApiOData.Feed(). I would suggest you go to Advanced Editor in Query Editor to replace SmartApiOData.Feed as OData.Feed then test again. 

2. Please open Excel, get data from the same OData URL to see if the same issue occurs. 

3. Please run the latest Power BI desktop version 2.61.5192.601. 

Best Regards,
Qiuyun Yu 

[DaveTheOx]

Hi @v-qiuyu-msft,

Our OData API requires OAuth2/OpenID Connecto so I am using a Power BI Desktop custom connector to handle the authn/authz part.   The connector simply wraps OData.Feed():

SmartApiODataFeedImpl = (url as text, optional headers as text) as table =>
let
headerRecord = if (headers = null) then null
else
try Expression.Evaluate(headers)
otherwise error Error.Record("Error",
Extension.LoadString("SmartApiOData.Feed.Error.Headers") &
Extension.LoadString("SmartApiOData.Feed.Parameter.headers.SampleValues")),
oDataOptions = [ODataVersion = 4, MoreColumns = true, IncludeAnnotations = "*", IncludeMetadataAnnotations = "*"],
content = OData.Feed(url, headerRecord, oDataOptions)
in
content;

Thanks,
Dave

[DaveTheOx]

@v-qiuyu-msft also I can provide you my custom connector if it would help. 

[v-qiuyu-msft]

Hi @DaveTheOx,

Firstly, I would like to know if the issue related to the customer connector. Have you tried normal OData feed connector to access the same OData URL? Does the same issue occur? 

Best Regards,
Qiuyun Yu 

[DaveTheOx]

@v-qiuyu-msft, the connector simply wraps OData.Feed as my code snippet showed.     I'll modify our API code to remove OAuth support and test again with OData.Feed and no custom connector.   It will fail.   Then you can try OData.Feed using our API URL and you will see the failure first hand.   

Note that it should not be so difficult to report issues in your product.   

Thanks,
Dave

[DaveTheOx]

@v-qiuyu-msft, in PBI Desktop, select an empty query and then advanced editor and paste this into the editor:

let
Headers = [Authorization = "Bearer eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsIng1dCI6InQ3a0c1UGZ1cXFfLXFtMzFYcDJobXhXY2FLTSIsImtpZCI6InQ3a0c1UGZ1cXFfLXFtMzFYcDJobXhXY2FLTSJ9.eyJpc3MiOiJodHRwczovL3NhbXRlc3Quc3BjbG91ZGRhdmUuY29tL3NhbS9vYXV0aC8iLCJhdWQiOiI1MkFCNUM5Qy04OTJDLTQ1RTctQjZFNy1GQUNBMzNGNDlGOEIiLCJleHAiOjE1Mzg3NjQwNzEsIm5iZiI6MTUzNjE3MjA3MSwiY2xpZW50X2lkIjoiMjU1Mzg2ZTYtMjRiNC00ZGE0LWFjY2ItZGUyNTViZmFhZjA0Iiwic2NvcGUiOlsiNTJBQjVDOUMtODkyQy00NUU3LUI2RTctRkFDQTMzRjQ5RjhCIiwib2ZmbGluZV9hY2Nlc3MiXSwic3ViIjoidGVzdHVzZXIiLCJhdXRoX3RpbWUiOjE1MzYxNzIwNjgsImlkcCI6Imlkc3J2IiwibmFtZSI6IlVzZXIsIFRlc3QiLCJpbmdyLnVzZXJfaWQiOiJmNzliNjkyMC1mNTdiLTQ1ZWMtYWU1My00MDNiODhhNGQ5MzEiLCJpbmdyLmdyb3VwX2lkIjoiZDA2NjkzYzMtZGE3Yi00YjkwLWE0YzgtYzAwNzAyMDRhNDg2Iiwicm9sZSI6IjF8QXxhZG1pbiIsImluZ3Iuc2Vzc2lvbl9pZCI6ImZjOTE4YjY1LTM5YjEtZTgxMS1iY2U3LTAwMGQzYTExNWZlYSIsImFtciI6WyJwYXNzd29yZCJdfQ.NYJ3WIGzBTbExX0NYf5znzZ9GuIdLOIZPCRP-fj8EbQImcRXb6t96qvAlR3MCz4UaIW2rzhW7IjQPO4S-UAqOGFCcuvtkbDHMfu3leqQjh4qFrHiL2RO8hx_h57Ex5mTePhx1XKHwzsan9PYJO24ES7spr50LWN8jwyyJI80Ly2EKYA2nzFR0eP-Nag-d880HonTgII9ddOzMPv-7qkKwTnwP0k5t5Xcu0Yoos2eWJqeTrZKYOOVdTeicr5coLEMhd11xoUe_m5em4ln2-7Si_45DJXOj4NhFZIP1jqDmVb2qCHeQdw3t1icpPv_LPMfVOeP6ybkgYY7k5P_dYMa0Q"],
Options = [ODataVersion = 4.0],
Source = OData.Feed("https://samtest.spclouddave.com/SampleServicePBI/Sppid/V2", Headers, Options)
in
Source

This results in the duplicate key error as I originally reported.   Now would you please file the issue?

Thanks,
Dave

[v-qiuyu-msft]

Hi @DaveTheOx,

Thanks for your information. I have reported this issue internally: CRI 83036326. Will update here once I get any information. 

Best Regards,
Qiuyun Yu 

[v-qiuyu-msft]

Hi @DaveTheOx,

I got information below: 

The code that processes Navigation Restriction Annotations seems to be assuming navigation paths are only one segment, probably because until recently we didn't support anything longer anyway. As such, the restrictions on "Items/Parent" and "Items/Descendants" look like duplicates to us, causing the error. This obviously is wrong, but the weird thing is that we don't seem to actually be using these navigation restrictions. Therefore, to get PBI to work, the customer may as well remove those navigation restrictions entirely; as long as the customer doesn't navigate into them we won't pull them.

Best Regards,
Qiuyun Yu