cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ericleigh007
Helper II
Helper II

OData.Feed command with query and options no working

Running into problems attempting to speed up data refresh by using a "trimmed" OData query.

 

let
//    this returns the "str cannot be null" error
//    Source = OData.Feed("https://rxxxxxx.sharepoint.com/sites/pe/_api/lists/getbyTitle('Project ETC')/Items?$orderby=Created desc&filter=Date ge datetime('2016-06-01T00:00:00Z'"
// this works okay, but of course my data is not filtered at all, and the extraction takes ages.
    Source = OData.Feed("https://raydon.sharepoint.com/sites/pe/_api/lists/getbyTitle('Project ETC')/Items",
                            null,
                            [
                                Query =
                                [
                                    rowlimit = Number.ToText(2)
                                ]
                            ]
                        )
in
    Source

 
Both Uri's work fine in a browser.

 

The longer Uri featuring the comparison of datetime(blah) actually causes an exception in the query in Power BI latest. 

 

DataSource.Error: Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed:
OData Version: 3 and 4, Error: The remote server returned an error: (400) Bad Request. (The query is not valid.)
OData Version: 4, Error: The remote server returned an error: (400) Bad Request. (The query is not valid.)
OData Version: 3, Error: The remote server returned an error: (400) Bad Request. (The query is not valid.)
Details:
    DataSourceKind=OData
    DataSourcePath=https://raydon.sharepoint.com/sites/pe/_api/lists/getbyTitle('Project%20ETC')/Items
    SPRequestGuid=79def49d-307a-3000-ffb9-f235daef118e, 79def49d-d08a-3000-ffb9-fe4f9814c431, 79def49d-8098-3000-ffb9-fc5db340bc42

 

BUT, even when that is trimmed off as an experiment, the OData "trimming", namely $top=1, for intance, doesn't seem to do anything.

 

In addition, adding the rowlimit = "1" to the query in the Query record of the options record doesn't trim the query at all either.

 

In must be obvious that users will want to limit their OData queries as much as possible, so how is this supposed to work?

 

The queries DO work in the web browser, against the service that I'm interested in (Sharepoint Online 2013).  Ive verified that hte filter element works to limit the data, AND the $top=n pruning method works as well.
 
I've been trying to get the docs team to expand the documentation for OData.Feed, but no luck so far.

 

-thanks

-e

 

 

6 REPLIES 6
L_Bernardino_BR
Regular Visitor

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:

data.PNG

 

And by adding some regular transformation steps, this:

table_ok.PNG

 

4) OData.Feed with 'Query' option can not append system query options to the URi, and such attempt will raise an error.

error.PNG

 

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:

table_ok.PNG

 

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.

v-haibl-msft
Microsoft
Microsoft

@ericleigh007

 

To limit OData queries, please take a look at this document to see if it helps.

 

Best Regards,
Herbert

Herbert,

 

Thanks for taking the time to do that web search.

 

I'm not using Sharepoint search at all, so that information, while correct, is not germain to the problem that I'm having with OData.Feed.

 

However, what I'm looking for is specific testing and reply to my questions.

 

My hypothesis is that something is broken, and the team should know.  I would also like to know if the team agrees with this and/or if the behavior is expected in the current version.

 

It took my a couple of hours to create all those test cases, do the tests, and trace/record the results, and then post the full information (twice, since the board timeed out on me the first time).  I think that effort deserves a much more detailed analysis and response.

 

If you need any more information to help actually duplicate the problem so that you can log internal problem reports or do internal collaboration, I'd be happy to help.  I'm sure you, I, and a lot of others would be interested in how to make this thing work as I think should be expected.

 

Keep in mind that the query works just great in the browser, returning results that are limited ($top=) and sorted ($orderby=Date desc) filtered ($filter=) based on the OData query.

 

thanks

-e

The right way to limit records read from an OData feed is with the function generated by the UI: Table.FirstN. Trying to manually construct OData URLs in this fashion is fairly poorly supported. The "bad request" message comes straight from SharePoint, of course, and is perhaps because the datetime literal is malformed according to the OData spec.

 

Naturally, using Json.Document(Web.Contents(url)) is going to be faster than OData.Feed(url). For one thing, it won't fetch or parse the metadata document, or try to convert the JSON values according to that metadata.

I just got time to get back to this.  Sorry for the delay.

 

I woudl like us to dedicate some time to this, becuase my results are much differnet than yours.

 

OData.Feed doesn't work as expected, and doesn't ahve documentation that makes it obvious to use.

 

To your point about the query being invalid, I can only say that the same query WORKS in the BROWSER.  i can enter a query like this:

 

https://rxxxxxxx.sharepoint.com/sites/pe/_api/lists/getbyTitle('Project ETC')/Items?$top=2000&$orderby=Created desc&filter=Date ge datetime('2016-06-01T00:00:00Z')

 

And it not only does not return an error, but sharepoint happily returns 1000 rows of data and orders them appropriately.  If I do the same thing with the OData.Feed function, the query is not filtered at all, or the query crashes.

 

I may be misunderstanding.  Are you saying that the OData.Feed query is folded by Table.TopN, like an SQL query woudl be?  I have not experienced that. 

 

Are you saying that the rest of the OData.Feed URL is just ignored, and the query is done some other way?   If so, how is one to use a query with OData.Feed?  The docuemntation isn't specific on how the elements of the record passed with OData.Feed are supposed to be used.

 

If you have time, can you inlcude in your next response a few examples of how to correctly use OData.Feed and its various options?  I think the rest of the user community would also be greatful.  (note I have a separate request here for fixing the docs on OData.Feed).

 

In my experiments, Web.Contents is faster becuase it uses the query, just like the web browser.  It may be bypassing other processing, as you siad, but it also saves a bunch of time becuase it doesn't blindly grab the entire list (in my case the list is just about 5000 entries).

 

Unfortunately, becuasse it doesn't present the data in PowerBI like the OData version, though it is faster, I cannot use Web.Contents even counpled with Json.Document.  The information I need is only returned properly (although VERY slowly) by OData.Feed.)   That's why I'm trying to use OData.Feed as efficiently as possible.

 

Thakns very much for taking the time.

-e

ericleigh007
Helper II
Helper II

Things are working differently with the OData.Feed than with Json.Document with the same URL, and for data from PWA (Project Online) compared to SharePoint Odata.

 

For the PowerBi team, though, there is definitely some sort of bug/limitation with the way that OData.Feed works that makes it much slower than Json.Document(Web.Contents... ) usage.

 

PWA Example:

 

Code:

let
    Source = OData.Feed("https://rxxxxxx.sharepoint.com/sites/pwa/_api/ProjectData/Projects?$top=3")
in
    Source

 

Trace:

Look good in the comment editor, so maybe view them there.

Also stored Word docs on Onedrive, public viewing.

https://1drv.ms/w/s!AuVy-U2vn_fU6MRr7f5LYtv5X19feg

 

Version:1.0 StartHTML:00000097 EndHTML:00001633 StartFragment:00000097 EndFragment:00001633

# Result Protocol Host URL Body Caching Content-Type Process Comments Custom

7005200HTTPSraydon.sharepoint.com/sites/pwa/_api/ProjectData/Projects?$top=316,376private, max-age=0; Expires: Wed, 10 May 2017 17:08:42 GMTapplication/json;odata=minimalmetadata;streaming=true;charset=utf-8microsoft.mashup.container.netfx40:15912  
7006200HTTPTunnel toraydon.sharepoint.com:4430  microsoft.mashup.container.netfx40:15912  
7007200HTTPSraydon.sharepoint.com/sites/pwa/_api/ProjectData/$metadata133,764private, max-age=0; Expires: Wed, 10 May 2017 17:08:45 GMTapplication/xml;charset=utf-8microsoft.mashup.container.netfx40:15912  
7010200HTTPSraydon.sharepoint.com/sites/pwa/_api/ProjectData/Projects?$top=316,376private, max-age=0; Expires: Wed, 10 May 2017 17:08:46 GMTapplication/json;odata=minimalmetadata;streaming=true;charset=utf-8microsoft.mashup.container.netfx40:15912  

 

 

Json.Document(Web.Contents(.... )  example:

 

Code:

let
    Source = Json.Document(
                   Web.Contents("https://rxxxxxx.sharepoint.com/sites/pe/_api/lists/getbytitle('Project ETC')/Items?$top=3",
                        [
                            Headers = [ #"Accept" = "application/json" ]
                        ])),
    value = Source[value],
    #"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"odata.type", "odata.id", "odata.etag", "odata.editLink", "FileSystemObjectType", "Id", "ServerRedirectedEmbedUri", "ServerRedirectedEmbedUrl", "Project", "Date", ... /// truncated
in
    #"Expanded Column1"

 

Trace:

Onedrive link: 

https://1drv.ms/w/s!AuVy-U2vn_fU6MRr7f5LYtv5X19feg

 

Version:1.0 StartHTML:00000097 EndHTML:00001937 StartFragment:00000097 EndFragment:00001937

# Result Protocol Host URL Body Caching Content-Type Process Comments Custom

8361200HTTPSraydon.sharepoint.com/sites/pe/_api/lists/getbytitle('Project%20ETC')/Items?$top=37,669private, max-age=0; Expires: Wed, 10 May 2017 17:27:12 GMTapplication/json;odata=minimalmetadata;streaming=true;charset=utf-8microsoft.mashup.container.netfx40:15912  
8362200HTTPSraydon.sharepoint.com/sites/pe/_api/lists/getbytitle('Project%20ETC')/Items?$top=37,669private, max-age=0; Expires: Wed, 10 May 2017 17:27:12 GMTapplication/json;odata=minimalmetadata;streaming=true;charset=utf-8microsoft.mashup.container.netfx40:15912  
8363200HTTPSraydon.sharepoint.com/sites/pe/_api/lists/getbytitle('Project%20ETC')/Items?$top=37,669private, max-age=0; Expires: Wed, 10 May 2017 17:27:12 GMTapplication/json;odata=minimalmetadata;streaming=true;charset=utf-8microsoft.mashup.container.netfx40:15912  
8364200HTTPSraydon.sharepoint.com/sites/pe/_api/lists/getbytitle('Project%20ETC')/Items?$top=37,669private, max-age=0; Expires: Wed, 10 May 2017 17:27:13 GMTapplication/json;odata=minimalmetadata;streaming=true;charset=utf-8microsoft.mashup.container.netfx40:15912  

 

 

 

OData.Feed example:

 

Code:

let
    Source = OData.Feed("https://rxxxxxx.sharepoint.com/sites/pe/_api/lists/getbytitle('Project ETC')/Items?$top=3")
in
    Source

 

Trrace:

Onedrive link: https://1drv.ms/w/s!AuVy-U2vn_fU6MRuWmJHALBRMB4_cQ

 

Version:1.0 StartHTML:00000097 EndHTML:00003204 StartFragment:00000097 EndFragment:00003204

# Result Protocol Host URL Body Caching Content-Type Process Comments Custom

7083200HTTPSraydon.sharepoint.com/sites/pe/_api/lists/getbytitle('Project%20ETC')/Items?$top=37,669private, max-age=0; Expires: Wed, 10 May 2017 17:09:56 GMTapplication/json;odata=minimalmetadata;streaming=true;charset=utf-8microsoft.mashup.container.netfx40:15912  
7084200HTTPSraydon.sharepoint.com/sites/pe/_api/$metadata1,384,338private, max-age=0; Expires: Wed, 10 May 2017 17:09:56 GMTapplication/xml;charset=utf-8microsoft.mashup.container.netfx40:15912  
7087200HTTPSraydon.sharepoint.com/sites/pe/_api/lists/getbytitle('Project%20ETC')/Items?%24skiptoken=Paged%3dTRUE%26p_ID%3d4&%24top=37,671private, max-age=0; Expires: Wed, 10 May 2017 17:09:59 GMTapplication/json;odata=minimalmetadata;streaming=true;charset=utf-8microsoft.mashup.container.netfx40:15912  
7088200HTTPSraydon.sharepoint.com/sites/pe/_api/lists/getbytitle('Project%20ETC')/Items?%24skiptoken=Paged%3dTRUE%26p_ID%3d7&%24top=37,697private, max-age=0; Expires: Wed, 10 May 2017 17:10:00 GMTapplication/json;odata=minimalmetadata;streaming=true;charset=utf-8microsoft.mashup.container.netfx40:15912  
7089200HTTPSraydon.sharepoint.com/sites/pe/_api/lists/getbytitle('Project%20ETC')/Items?%24skiptoken=Paged%3dTRUE%26p_ID%3d27&%24top=37,680private, max-age=0; Expires: Wed, 10 May 2017 17:10:01 GMTapplication/json;odata=minimalmetadata;streaming=true;charset=utf-8microsoft.mashup.container.netfx40:15912  
7090200HTTPSraydon.sharepoint.com/sites/pe/_api/lists/getbytitle('Project%20ETC')/Items?%24skiptoken=Paged%3dTRUE%26p_ID%3d43&%24top=37,705private, max-age=0; Expires: Wed, 10 May 2017 17:10:01 GMTapplication/json;odata=minimalmetadata;streaming=true;charset=utf-8microsoft.mashup.container.netfx40:15912  
7091200HTTPSraydon.sharepoint.com/sites/pe/_api/lists/getbytitle('Project%20ETC')/Items?%24skiptoken=Paged%3dTRUE%26p_ID%3d52&%24top=37,708private, max-age=0; Expires: Wed, 10 May 2017 17:10:03 GMTapplication/json;odata=minimalmetadata;streaming=true;charset=utf-8microsoft.mashup.container.netfx40:15912  

 

 

. . . one record with a different ID for each Item.

 

 -thanks

-e

 

 

 

 

 

 

 

 

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors