Forum Discussion

pmscorca's avatar
pmscorca
Kudo Kingpin
11 months ago

Managing pagination for a Web API connector used in a dataflow gen2

Hi,

I need to create a dataflow gen2 that has to use a Web API connector to read Dynamics 365 Business Central data.

This connector requires to manage the pagination else it allows to read only the first 20.000 rows.

I've read that I need to manage the pagination by a Power Query function, while with a data pipeline isn't required a such management.

Does it exist a more easy manner in order to use a Web API connector in a dataflow gen2 to read an entire BC table?

Many thanks

Dataflow
General Question

5 Replies

  • v-lgarikapat's avatar
    v-lgarikapat
    Community Support
    11 months ago

    Hi pmscorca , 

    Thanks for reaching out to the Microsoft fabric community forum.

     

    Power Query Function (Manual Pagination)

    Create a recursive Power Query function that:

    Makes the initial API call

    Checks for @odata.nextLink

    Continues fetching until no more pages remain

    You’ll need to use Json.Document(Web.Contents(...)) and loop through pages using List.Generate or similar constructs.

    This method works well but requires careful error handling and throttling logic.Use

     

    Data Pipeline  Approch 

    Data Pipelines in Fabric handle pagination automatically for many connectors, including Business Central. If your use case allows, this is the simpler and more scalable route.

    Pipelines abstract away the paging logic

    You can dump the full dataset into a Lakehouse or Warehouse

    Then use Dataflow Gen2 to transform it downstream

     

    Solved: Power Query Rest API Paging with @odata.nextLink - Microsoft Fabric Community

    TripPin 5 - Paging - Power Query | Microsoft Learn

    Solved: Fabric Dataflow Gen2's Business Central connector ... - Microsoft Fabric Community

     

    We truly appreciate your continued engagement and thank you for being an active and valued member of the community.

    If you're still experiencing any challenges, please don’t hesitate to reach out  we’d be more than happy to assist you further.

    We look forward to hearing from you.

    Best regards,
    Lakshmi

    • pmscorca's avatar
      pmscorca
      Kudo Kingpin
      11 months ago

      Ok, but in some scenarios it could be more useful a Dataflow gen2 , on other ones a Data pipeline.

      So, it should be appreciated to use a managed pagination for a Web API connctor in a Dataflow gen2 avoinding the manual pagination.

      • v-lgarikapat's avatar
        v-lgarikapat
        Community Support
        11 months ago

        Hi pmscorca ,

        Thanks for the follow-up questions

         

        In many scenarios, Dataflow Gen2 is the preferred choice for lightweight ingestion and transformation workflows, especially when direct integration with Lakehouses is needed. However, when consuming data from paginated REST APIs, the lack of native managed pagination becomes a significant limitation.

        Currently, users must implement manual pagination using recursive Power Query M scripts, which can be complex, error-prone, and difficult to maintain  particularly for APIs using cursor based tokens, header-driven continuation, or dynamic termination conditions.

         

         

        This appears to be a current limitation in Power BI. I would recommend submitting this scenario as a feature request on the official Power BI Ideas forum. The Product Team actively monitors suggestions there, and items with strong community support are often considered for future enhancements.

        You can submit your idea here

        We appreciate your collaboration and support!

        Best Regards,
        Lakshmi.