Help with connection

Hi Jc_16 ,

To import all the information from a paginated API, you can modify your Power Query code to loop through the pages and combine the results. Here's an example of how you can achieve this:

let

    GetPage = (page as number) =>

        let

            Source = Json.Document(Web.Contents(

                baseUrl & "/api/" & accountKey & "/" & subscriptionKey & "/billing/invoices?page=" & Text.From(page) & "&pageSize=10",

                [

                    Headers = [

                        #"Content-Type"="application/x-www-form-urlencoded",

                        #"Authorization"="Bearer " & token

                    ]

                ]

            )),

            Data = Source[data]

        in

            Data,



    CombinePages = List.Generate(

        () => [Page = 1, Data = GetPage(1)],

        each List.Count([Data]) > 0,

        each [Page = [Page] + 1, Data = GetPage([Page] + 1)],

        each [Data]

    ),



    CombinedData = List.Combine(CombinePages)

in

    CombinedData

This code defines a function GetPage that takes a page number as an argument and returns the data for that page. Then, it uses List.Generate to loop through the pages until an empty page is encountered. Finally, it combines the data from all pages into a single list using List.Combine.

Replace your existing code with this modified version, and it should import all the information from the paginated API.

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.