PowerBI desktop vs OData Feed limit

[former_member1375708]

I am trying to get data from SAP ByDesign in PowerBI by using OData feed.

When opening the data in power query I get to see all rows. But when I try to use the data I only see 50 rows.

I even tried $top=1000 but still I can only use 50 rows.

I am using the following URL:

OData.Feed("https://myXXXXXX.sapbydesign.com/sap/byd/odata/ana_businessanalytics_analytics.svc/RPZ9E4AD0C6FA59DE7087CF2FQueryResults?saml2=disabled$top=1000$format=json")

Do you have a solution?

[danielzhou]

Hi Addis,

The $top option should work with ByDesign. We use it with PowerBI.

The URL should use the "&" to separate oData options. The query string should look like "?$top=1000&$format=json". I have no idea on the saml2 option.So, I did not include it in the above sample.

Good luck!

Daniel

[VVancalbergh]

OData will return data in "pages". That is, it will provide you the first x rows and you then have to poke it again to get the next x rows. In each response, the URL for the next chunk is embedded at the tail end in @odata.nextLink.

See also here: https://learn.microsoft.com/en-us/power-query/samples/trippin/5-paging/readme

The pagesize (how many rows a service returns) is primarily dictated by the service itself. Your $top parameter is just how many you, as a receiver, can process in one go. But the sender can lower the number.

SAP ByDesign will reduce the pagesize based on how "big" each row is. So if you query a small table (without $top parameter), SAP might return 1000 rows. If you query a complex object you might only get back 50. This is dynamic, so selecting only one level on a complex object and requesting a small number of columns will suddenly also return more rows. If you use the expand option to get more levels in one go you'll get less rows per page.

Your query mechanism should therefore keep getting the next page as long as @odata.nextLink has a value (unless you hit a maximum of rows you can handle).