question

ChrisCarter-3507 avatar image
0 Votes"
ChrisCarter-3507 asked eric-msft edited

Power query removes slash from URL in OData navigation property

We have an OData feed where the entity IDs are in the form of URLs, like:

http://data.mycompany.com/1234

We can load the main OData feed into Power Query just fine, but when we try to expand navigation properties it seems that PowerQuery removes one of the two forward slashes after the http protocol.

So when it should be making a query to something like this:

http://data.mycompany.com/odata/feed('http://data.mycompany.com/1234')/NavProperty

Instead it tries to query:

http://data.mycompany.com/odata/feed('http:/data.mycompany.com/1234')/NavProperty

And obviously this doesn't work.

I found that by explicitly specifying an @odata.id annotation in the feed we can influence the URL that Power Query tries to follow, but only double-encoding the slashes can I get it to work in Power Query. So with this in the feed:

"@odata.id": "http%3A%252F%252Fdata.mycompany.com/1234"

Power Query makes the needed query:

http://data.mycompany.com/odata/feed('http://data.mycompany.com/1234')/NavProperty

(Note that all of the encoding gets removed).

Unfortunately, this breaks the API for other clients so it isn't a good fix for us.

We're thinking about changing our API to detect "http:/xxx" in IDs and add the extra slash back in, but this feels like a hack and we'd prefer to get it to work the way we think it should.

Has anyone come across an issue like this?

Thanks in advance

power-query-not-supported
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

eric-msft avatar image
0 Votes"
eric-msft answered eric-msft edited

You may just have to support encodings in your OData feed; a lot of HTTP frameworks treat encodings as something they can freely substitute in and out, and unfortunately that often includes .NET; we have no control over what it does at the last minute to the URLs we send. I admit I haven't yet had a chance to check if slash is covered by this behavior; .NET does document such cases in their breaking changes (because they changed how this was handled from 3.5 to 4.0). I have definitely seen it in other cases of reserved characters, and I am pretty sure forward slash is a reserved character due to its special role as a separator in URIs.

I will continue looking to see if the OData spec has anything to say about using URIs like this, though I would be surprised if it was allowed.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.