Need help understanding why an OData endpoint is not working in dynamics/powerbi/power query

Question

Hi All

Our platform allows users to configure and publish custom data endpoints for external consumption. We recently added the ability to surface the data as an OData feed, specifically for consumption in Dynamics and PowerBI.

As it stands we have the feeds being consumed successfully in C# code (via the OData connected service extension in Visual Studio) but when we try and access the endpoint in Dynamics or PowerBI we get a generic error message. We have tried simulating the request in PowerQuery and receive the following message.

We’ve tried comparing the data visually to reference datasources but so far are drawing a blank.

If there is someone there who can help it would be very much appreciated. An example of the data source would be https://kkapi.azure-api.net/api/Data/v1/eb68775b-c38a-490d-8866-ac1d0dc415e7/Employees?subscription-key=0c04173f848d4d37a56ae54bd93a5118

Answer 1

To let anyone who may come across this while trying to generate their own OData serialisation.

The problem turned out to be nothing to do with the textual format of my response. I had that right.

However, I had missed that the standard says that OData responses SHOULD include a "OData-Version" HTTP header (odata-v4.01-part1-protocol.html)

It appears the Mashup engine treats that as MUST (anyone who's ever had to deal with RFCs will know the importance of the phrasing)

Once the Http header is added and I checked the "Include open type columns" checkbox in the OData feed advanced options I was able to access the data in PowerQuery (still need to test in PowerBI and Dynamics, but feeling a lot more confident now)

If you've got a reference in your code to Microsoft OData nugets you should be able to handle it with the following code:

response.Headers.TryAddWithoutValidation( "OData-Version", ODataUtils.ODataVersionToString(ODataVersion.V4));

Still some more testing to do, but hopefully this helps others in the future

All the best

Mark

Answer 2

Hi @Mark Middlemist

I won't be able to help with this and I feel a bit alone these days helping on Power Query issues. Couple of suggestions in the meantime

1/ Check the following threads that relate to the same error and seem to indicate this could be due to an Authentication issue

• https://social.technet.microsoft.com/Forums/en-US/b806a862-278c-46a7-9c33-2db90958b756/odata-feed-throws-error-odata-the-given-url-neither-points-to-an-odata-service-or-a-feed?forum=powerquery
• https://social.technet.microsoft.com/Forums/en-US/12e6e0d3-356a-4280-b0c1-532f4a5b85cb/connection-to-hosted-crm?forum=powerquery

2/ Search the former Power Query forum as I only picked the above 2
3/ Search the PowerBI Community (I did not)

If nothing helped feel free to raise your case on the PowerBI Community where there's obviously more helpers
Sorry that I can't do more. All the best...

Answer 3

Hi @Mark Middlemist

After many searches on the Net, readings here and there, if you haven't read the below threads IMHO you should:

• https://stackoverflow.com/questions/50264739/how-to-specify-an-api-key-name-in-excel-a-web-api-key-can-only-be-specified-wh
• https://stackoverflow.com/questions/33225590/authenticating-with-excel-power-query-against-net-odata-web-api

Unfortunately nothing suggested in these threads work with the URL you shared (I tested a number of variationsss)
However, and even if I haven't found something saying this is forbidden/not recommended I wonder if the name of your API Key (subscription-key) isn't causing a problem somewhere due to the dash it includes. If you have a test environement that allows easily changing/creating an API Key name w/o a dash maybe you could give it a try

In which case, in Power Query, that would look something like:

= OData.Feed("https://kkapi.azure-api.net/api/data/v1/eb68775b-c38a-490d-8866-ac1d0dc415e7/Employees", [ApiKeyName="MySubscriptionKey"])  

Eventually:

= OData.Feed("https://kkapi.azure-api.net/api/data/v1/eb68775b-c38a-490d-8866-ac1d0dc415e7/Employees", [Implementation="2.0", ApiKeyName="MySubscriptionKey"])   

Then when prompted to provide the Credentials > Web API > 0c04173f848d4d37a56ae54bd93a5118

Something I didn't mention earlier and in case you're not aware: the Credentials are stored in the Data source settings. Before any test it's a good idea to clear the Permissions

Answer 4

OK, first thing I've found, the tracing is working (contrary to what the docs I had been looking at said the files are in C:\Users\<Username>\AppData\Local\Microsoft\Office\16.0\PowerQuery\Traces)

In there I'm seeing messages like the following:

DataMashup.Trace Warning: 24579 : {"Start":"2021-07-05T15:29:26.5095988Z","Action":"SimpleDocumentEvaluator/GetResult<IPreviewValueSource>","HostProcessId":"21916","PartitionKey":"Section1/Query2/Source","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine1.Runtime.ValueException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: [DataFormat.Error] OData: The given URL neither points to an OData service or a feed: 'https://kkapi.azure-api.net/api/Data/v1/eb68775b-c38a-490d-8866-ac1d0dc415e7/Employees?subscription-key=0c04173f848d4d37a56ae54bd93a5118'.\r\nStackTrace:\n at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.<>c__DisplayClass3_0.<BeginGetResult>b__0(EvaluationResult2`1 result)\r\n\r\nDetail: \"https://kkapi.azure-api.net/api/Data/v1/eb68775b-c38a-490d-8866-ac1d0dc415e7/Employees?subscription-key=0c04173f848d4d37a56ae54bd93a5118\"\r\n\r\n","ProductVersion":"2.94.222.0 (21.06)","ActivityId":"f4689a20-9d57-448f-b446-64084aadf0e6","Process":"Microsoft.Mashup.Container.Loader","Pid":21188,"Tid":1,"Duration":"00:00:00.3646210"}

If anyone knows of a way to get more specific detail on validator failures that would be brilliant.

Thanks in advance

Mark