OData feed unconnectable: The odata.context url is invalid

[baoyan]

We got the error message: "The odata.context url 'https://ourservice.com/$metadata#TestNode(toTestNode())' is invalid" when we tried to connect to our server with odata expand operation.

The odata feed url is like "https://ourservice.com/TestNode?$expand=toTestNode", and our server use Odata Core/Edm 7.5.4, the metadata url 'https://ourservice.com/$metadata#TestNode(toTestNode())' is auto generated by OData, why the Power Query marked it as invalid url and disconnected?

[LEliJones]

We digged a bit further on this issue. The breaking change appears where Microsoft.OData.Core, goes from version 7.5.1 to 7.5.2. I found a github pull https://github.com/OData/odata.net/pull/1286 where they mention changing the context to reflect expands. There is also a closed issue and pull request (https://github.com/OData/odata.net/pull/2086) to put asterisks into the empty parenthesis that was rejected because the odata spec doesn't require asterisks - basically Power BI is no longer performing to spec. Guess we are out of luck.

[baoyan]

Is there anyone meet the same issue?

[LEliJones]

Same with our Odata server.

Using Microsoft.AspNet.OData 7.0.0 works

Using Microsoft.AspNet.OData 7.7.6 fails with the same message as above in Power BI.

[rainers]

Same problem here. Did you find any solution?

[baoyan]

We degrade Odata version to 7.4.1, it can work fine with PowerQuery

[rainers]

Hmm, interesting. I downgraded to 7.4.1, too, but $expand still produces the error in PowerBI. Outside of PowerBI everything works fine though.

[dax]

Hi @baoyan , 

I am not sure whether this is caused by the URL or credential, please make sure your URL is correct, and you could try to delete the premission in datasource setting, then try type it when you connect Odata to see whether it work or not(please make sure the credential is correct). By the way, you also could refer to How to connect an OData Feed to Microsoft Power BI for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[baoyan]

Hi @dax ,

Thanks for your reply.

I have tested a lot for this, and I'm sure it's not the permission problem since I disabled permission check for my service locally.

And as I known, the Odata 7.4.1 generated odata.context URL is always valid for Power Query, its context.url template is always http://servicedomain/$metadata#entity , while the template of Odata 7.5.4 is more complex, it can be http://servicedomain/$metadata#entity(property()).

So I actually want to know is that whether the Power Query not support the metadata url format of OData 7.5.4, are you plan to support this url format.

[fedebona]

Hi, I'm having the same issue referencing a saved query in Dynamics365: 

DataFormat.Error: The odata.context url 'https://zenithcrm-uat.crm4.dynamics.com/api/data/v8.0/$metadata#accounts(telephone1,core_houseno,add...())' is invalid

Context url contains properties in brackets and this seems unsupported by PowerBI.

Is there any workaround?

[baoyan]

We mitigate this by degrading it to old version

[fedebona]

@baoyan , do you mean degrading API version used to access D365? How can I achieve this? 

[baoyan]

I mean degrade Odata version to 7.4.1 in my api implementation