Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.....
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).
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
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.
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
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
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
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |