Hi there.
This topic seems to me unanswered. That's acceptable, since the documentation around it is not crystal clear. I'll try to help.
Short answer: you can not append system query options (e.g. $select, $expand, $filter, $top) to the URi used into OData.Feed by using the optional parameter called 'Query'. The 'Query' optional parameter of the OData.Feed function only accepts custom query options (e.g. debug-mode). See Query Options Overview.
Solution: in order to append system query options to the URi used into OData.Feed , you must append them directly to the main URi. Or you can use Web.Contents which accepts system query options into the 'Query=[...]' clause.
However, OData.Feed function seems to return all the rows for an OData Service, while Web.Contents might not retrieve all the rows for an OData Service dataset (e.g. ProjectData from PWA, as you can see here).
Explanation:
1) OData.Feed is a power query function that supports optional parameters, including one called 'Query'
2) Web.Contents is also a power query function that supports optional parameters, including one called 'Query'
3) Although both parameters are called 'Query' and look the same, they are not. Here is the syntax:
OData.Feed(URL,[headers],[options])
Web.Contents(URL,[options])
4) Web.Contents with 'Query' option appends both system query options and custom query options to the URi:
So this...
let
Source =
Web.Contents(
"https://services.odata.org/V4/Northwind/Northwind.svc/Customers",
[
Query =
[
#"$select" = "CompanyName,Phone",
#"$top" = "2"
]
]
)
in
Source
... is the same as this:
let
Source = Web.Contents("https://services.odata.org/V4/Northwind/Northwind.svc/Customers?$select=CompanyName,Phone&$top=2")
in
Source
On both cases, I get this:
And by adding some regular transformation steps, this:
4) OData.Feed with 'Query' option can not append system query options to the URi, and such attempt will raise an error.
Try it yourself using the following code:
let
Source =
OData.Feed(
"https://services.odata.org/V4/Northwind/Northwind.svc/Customers"
,null,[Implementation = "2.0",
Query =
[
#"$select" = "CompanyName,Phone",
#"$top" = "2"
]
]
)
in
Source
Note 1: that error message is telling you that 'custom query options' can not start with '$', which is the symbol used to indicate a system query option, meaning, for OData.Feed, the 'Query' parameter can't handle $select and his friends $expand, $filter.
Note 2: you might think that removing the '$' from the code would make it work. Well, it will not raise an error, but removing the '$' tells the OData Service that it isn't a system query option, so you won't be able to perform filtering, selecting, etc. Tried that. Failed too.
5) Rather, append your system query options (e.g. $select, $top, $filter, etc) directly to the URi:
let
Source = OData.Feed("https://services.odata.org/V4/Northwind/Northwind.svc/Customers?$select=CompanyName,Phone&$top=2",
null,[Implementation="2.0"]
)
in
Source
Which will bring you this:
Additional comments:
1) Web.Contents is a 'wider' function than OData.Feed, in the sense that it is capable of retrieving data from any web source, while OData.Feed only retrieves data from OData Service. However, OData.Feed might be more capable when handling OData specifics by using optional parameters native for this function, as described here.
2) Web.Contents might not retrieve with ease all rows of an OData Service dataset, while OData.Feed does (or seems to while I was testing). See limits here.
3) When dynamically querying, Web.Contents needs 'RelativePath' and 'Query' parameters for the matters of consistent and successful authentication and refreshing on Power BI Online, regardless of the web source. I think OData.Feed is flawless at that point, since it doesn't handle the dynamic predicates as optional parameters outside the main URi. In fact, 'RelativePath' isn't even available for OData.Feed. See Chris Webb's post, Stacia Varga's post, and Patrick LeBlanc's video for further explanations.
4) The error occurs when dynamically generating the URi predicate with an ampersand '&' (e.g. looping methods or page ids). However, if using 'Query' and 'RelativePath' for the predicates, instead of '&', it'll be OK. Elaborating a bit, imagine an approach like "www.URi.com/method/page=" & X, being X your variable from 1 to n, resulting www.URi.com/method/page=1, then www.URi.com/method/page=2, then page=3... until page=n. Power BI Online for sure will not attempt authentication N-times. Therefore, it will require the use of 'RelativePath' and 'Query' in order to resolve a single main URi (www.URi.com/), validate the credentials provided, and establish gateway scheduled refresh or whatever. If that's your scenario, go with Web.Contents.
Helpful resources
Power BI September 2023 Update
Take a look at the September 2023 Power BI update to learn more.
Exclusive opportunity for Women!
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
Power Platform Conference-Power BI and Fabric Sessions
Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.
