Odata fetches more records than needed

[KJanssens]

I have a Odata feed (from Dynamics 365 Finance and Operations) through which I want to fetch the last X orders.

When I fetch the last 9999 orders, it gets fetched quite fast. However, when I want to fetch more than 10k orders, I see (by using Fiddler) that it tries to get ALL orders (in multiple batches of 10k) before it filters out (locally) only the top X orders... This means it wants to fetch millions of records...

Is this normal behaviour?

Thanks,

Koen

[v-lid-msft]

Hi @KJanssens ,

What is the kind of way you use to get last N orders , add url parameter in odata feed or use Table.SelectRows() ? Have you used filter step at the next step of Source step?

Please don't have any Confidential Information or Real data in your reply.

Best regards,

[KJanssens]

@v-lid-msft  I made some tests:

Unfiltered

let
    Source = OData.Feed("https://xxx.sandbox.operations.dynamics.com/data", null, [Implementation="2.0"]),
    SalesOrderHeaders_table = Source{[Name="SalesOrderHeaders",Signature="table"]}[Data]
in
    SalesOrderHeaders_table

   Result:  

• In Power Query Editor, preview is generated with request "GET /data/SalesOrderHeaders?$top=1000"
• In Power BI Desktop, the data gets loaded with multiple consequent requests (as expected):
  • GET /data/SalesOrderHeaders
  • GET /data/SalesOrderHeaders?$skip=10000&$top=10000
  • GET /data/SalesOrderHeaders?$skip=20000&$top=10000
  • ... and so on until all records are fetched (which takes ages)

Filtered in feed request (top 9999)

let
    Source = OData.Feed("https://xxx.sandbox.operations.dynamics.com/data/SalesOrderHeaders?top=9999", null, [Implementation="2.0"])
in
    Source

Result:

• Preview generated with request: "GET /data/SalesOrderHeaders?top=9999 "
• Full data load generates following (too many) requests:
  • GET /data/SalesOrderHeaders?top=9999
  • GET /data/SalesOrderHeaders?top=9999&$skip=10000&$top=10000
  • GET /data/SalesOrderHeaders?top=9999&$skip=20000&$top=10000
  • and so on...

Filtered in feed request (top 9999) combined with FirstN(9998)

let
    Source = OData.Feed("https://xxx.sandbox.operations.dynamics.com/data/SalesOrderHeaders?top=9999", null, [Implementation="2.0"]),
    #"Kept First Rows" = Table.FirstN(Source,9998)
in
    #"Kept First Rows"

Result:

• Preview generated with request: "GET /data/SalesOrderHeaders?top=9999 "
• Full data load generates only one request (as expected):
  • GET /data/SalesOrderHeaders?top=9999

Filtered in feed request (top 12345) combined with FirstN(10001)

let
    Source = OData.Feed("https://xxx.sandbox.operations.dynamics.com/data/SalesOrderHeaders?top=12345", null, [Implementation="2.0"]),
    #"Kept First Rows" = Table.FirstN(Source,10001)
in
    #"Kept First Rows"

Result:

• Preview generated with request: "GET /data/SalesOrderHeaders?top=12345"
• Full data load generates following requests (as expected):
  • GET /data/SalesOrderHeaders?top=12345
  • GET /data/SalesOrderHeaders?top=12345&$skip=10000&$top=10000
  • This last request is quite weird though, however it returns the correct amount of records

Filtered with FirstN(9999)

let
    Source = OData.Feed("https://xxx.sandbox.operations.dynamics.com/data", null, [Implementation="2.0"]),
    SalesOrderHeaders_table = Source{[Name="SalesOrderHeaders",Signature="table"]}[Data],
    KeptFirstRows = Table.FirstN(SalesOrderHeaders_table,9999)
in
    KeptFirstRows

Result:

• Preview generated with request: "GET /data/SalesOrderHeaders?top=1000"
• Full data load generates following request (as expected):
  • GET /data/SalesOrderHeaders?top=9999

Filtered with FirstN(10001)

let
    Source = OData.Feed("https://xxx.sandbox.operations.dynamics.com/data", null, [Implementation="2.0"]),
    SalesOrderHeaders_table = Source{[Name="SalesOrderHeaders",Signature="table"]}[Data],
    KeptFirstRows = Table.FirstN(SalesOrderHeaders_table,10001)
in
    KeptFirstRows

Result:

• Preview generated with request: "GET /data/SalesOrderHeaders?top=1000"
• Full data load generates following request (too many):
  • GET /data/SalesOrderHeaders?top=10001
  • GET /data/SalesOrderHeaders?$skip=10000&$top=10000
  • GET /data/SalesOrderHeaders?$skip=20000&$top=10000
  • ... and so on ...

Note that for all of the above scenarios, the first request of a series of requests is always done twice, and it also (almost always) generates requests "GET /data/$metadata"   and "GET /data" which is unnecessary IMHO...