Odata V4 Error. Couldn't parse OData response result.

[MikeSmallwood]

I'm using OData V3 and V4 for retrieving data in Power BI Desktop.

I get the following error when trying to execute my Query from Power BI Desktop.

"DataSource.Error: We couldn't parse OData response result.  Error: The context URI 'http://meta-ltp-yoga.metaphorix.co.uk:7048/DynamicsNAV100/ODataV4/$metadata#Collection(NAV.Inventory...)' is not valid for the expected payload kind 'Collection'.

Details:

    DataSourceKind=OData

    DataSourcePath=http://meta-ltp-yoga.metaphorix.co.uk:7048/DynamicsNAV100/ODataV4/Company('CRONUS%20UK%20Ltd.&#3....

If I retype "http://Meta-Ltp-Yoga.metaphorix.co.uk:7048/DynamicsNAV100/ODataV4/Company('CRONUS UK Ltd.')/InventoryMovements" into a browser data is returned correctly.  

Can anyone advise where the problem might be? (OData V3 works fine).

[v-shex-msft]

Hi @MikeSmallwood,

Based on error message, it mentioned invalid 'collection' variable in your query.
I don't think you can't direct use Odata V3 query in Odata V4.(it may contains some update for using parameter/variable)

I'd like to suggest your refer to the V4 document to create new query for this and try again.

What’s New in OData Version 4.0

Regards,

Xiaoxin Sheng

[MikeSmallwood]

Hi @v-shex-msft

Thanks for the response. I've looked at the documentation and my syntax looks OK. I'm struggling to apply any filter context inside an OData.Feed function.

Example 

Let

OdataFeed4 = "http://Meta-Ltp-Yoga.metaphorix.co.uk:7048/DynamicsNAV100/ODataV4/Company('CRONUS UK Ltd.')/PurchaseOrders?$filter=Type eq 'Item'",
Source = OData.Feed(OdataFeed4)

in

Source

The URL works fine in a browser.

Any further help would be much appreciated. 

[v-shex-msft]

Hi @MikeSmallwood,

I think you can try to use below formula, power query keywords are case sensitive, so you can't use 'Let' as start or the formula.

let
    OdataFeed4 = "http://Meta-Ltp-Yoga.metaphorix.co.uk:7048/DynamicsNAV100/ODataV4/Company('CRONUS UK Ltd.')/PurchaseOrders?$filter=Type eq 'Item'",
    Source = OData.Feed(OdataFeed4)
in
    Source

Regards,

Xiaoxin Sheng

[MikeSmallwood]

Hi @v-shex-msft

'Let" was just a typo in my message. I am using 'let' in my query.

Just t be clear this works fine in a browser but not in thr OData.Feed function .

Regards,

Mike     

[v-shex-msft]

Hi @MikeSmallwood,

Based on your description, it sounds like credential related issue. I'd like to suggest you use inprivate mode to test in web browser or test on power bi desktop after clean up stored odata credentials and cache files.

Regards,

Xiaoxin Sheng

[MikeSmallwood]

Hi @v-shex-msft,

The credentials are fine and the URL string works fine in a browser.

The URL string also works fine in the OData.Feed function under V3 (i.e. if I replace "ODataV4" with just "OData" in the URL).

There must an issue with the syntax of the string for OData V4 or an issue with Power BI Desktop.

Can you confirm via an example that you know works?

Kins regards,

Mike

[v-shex-msft]

Hi @MikeSmallwood,

Maybe you can try to add page parameter into request url or direct use odata as the parameter:

let
    OdataFeed4 = "http://Meta-Ltp-Yoga.metaphorix.co.uk:7048/DynamicsNAV100/ODataV4/Company('CRONUS UK Ltd.')/Page/PurchaseOrders?$filter=Type eq 'Item'",
    Source = OData.Feed(OdataFeed4)
in
    Source

>>The URL string also works fine in the OData.Feed function under V3 (i.e. if I replace "ODataV4" with just "OData" in the URL).

BTW, I double check on newest version official document and not found v4/v3 exist in that query string.

Using Filter Expressions in OData URIs

Notice: if your data contains any privacy data, please do mask sensitive data before sharing.

Regards,

Xiaoxin Sheng

[MikeSmallwood]

Hi @v-shex-msft,

When NAV 2016 introduced OData V4 support they provided a seperate URL for the V4 published web service. The only difference is the "V4" on the end of OData in the URL (see below).

The URL works in a browser with and without the "V4" but presents different results based on the V4 protocol (see below). The OData.Feed function will only work without the "V4".      

I need to understand how to use OData.Feed to get the V4 output. Is there anyone who can point me in the right direction?

Kind regards,

Mike       

let
Source = OData.Feed("http://Meta-Ltp-Yoga.metaphorix.co.uk:7048/DynamicsNAV100/ODataV4/Company('CRONUS%20UK%20Ltd.')/PurchaseOrders?$select=Buy_from_Vendor_No, Document_No&$filter=Buy_from_Vendor_No eq '30000'")
in
Source

V3 URL Result 

V4 URL Result