topic Re: How to limit OnTake to 100 rows in Custom Connector in Developer

How to limit OnTake to 100 rows in Custom Connector

JoeFields — Fri, 01 Sep 2023 00:51:24 GMT

I am trying to implement Table.View with a REST API but the API has a limit of 100 items per call. I saw PQ trying to fetch 200 items at a time which caused errors so I modified the OnTake handler as below but now it loads 100 rows and then has an error on row 101. I suspect this is because it is still trying to fetch 200 rows but only getting back 100 but how do I override the max number of rows requested?

OnTake = (count as number) => let newState = state & [Top = (if count > 100 then 100 else count)] in @View(newState),

Re: How to limit OnTake to 100 rows in Custom Connector

Anonymous — Mon, 04 Sep 2023 01:18:59 GMT

Hi @JoeFields,

I'd like to suggest you take a look at following link about use power query handling pagination api if helps:

Handling paging for Power Query connectors - Power Query | Microsoft Learn
Regards,

Xiaoxin Sheng

Re: How to limit OnTake to 100 rows in Custom Connector

JoeFields — Tue, 05 Sep 2023 15:38:18 GMT

Hello Xiaoxin, we are implementing paging already in our connector which works well but it is downstream from the OnTake so this would not resolve the issue. It appears that OnTake is set to receive a default of 200 items per page and there is not a clear way to override this default to a different value. If I cap OnTake to 100 results, we get errors because it is expecting 200 results.

Re: How to limit OnTake to 100 rows in Custom Connector

Anonymous — Wed, 06 Sep 2023 07:41:30 GMT

HI @JoeFields,

I'd like to suggest you filter on the output result to limit the result amount instead of the directly overwrite the parameters. (current power bi data connectors does not include any writeback features)

So if these properties can't be configured/defined in the API request, it may means you also can't customize them at power bi desktop side.

Regaerds,

Xiaoxin Sheng