Need Help with Stripe REST API Pagination in Power Query/M

Hi rsderby68 

Try this code

let
    Limit = "6",    

    APICall = 

        List.Generate
        (  
            () => [ APIData = Json.Document(Web.Contents("https://api.stripe.com/v1/charges?limit="&Limit)) , MoreData = APIData[has_more] , starting_after = APIData[data]{List.Count(APIData[data]) - 1}[id] ],

            each [MoreData] = true,
           
            each [ APIData = Json.Document(Web.Contents("https://api.stripe.com/v1/charges?limit="&Limit&"&starting_after="&[starting_after])) , MoreData = [APIData][has_more] , starting_after = APIData[data]{List.Count([APIData][data]) - 1}[id] ],

            each [APIData]
        ),
    #"Converted to Table" = Table.FromList(APICall, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"data"}, {"data"}),
    #"Expanded data" = Table.ExpandListColumn(#"Expanded Column1", "data"),
    #"Expanded data1" = Table.ExpandRecordColumn(#"Expanded data", "data", {"id", "object", "amount", "amount_captured", "amount_refunded", "application", "application_fee", "application_fee_amount", "balance_transaction", "billing_details", "calculated_statement_descriptor", "captured", "created", "currency", "customer", "description", "destination", "dispute", "disputed", "failure_balance_transaction", "failure_code", "failure_message", "fraud_details", "invoice", "livemode", "metadata", "on_behalf_of", "order", "outcome", "paid", "payment_intent", "payment_method", "payment_method_details", "receipt_email", "receipt_number", "receipt_url", "refunded", "refunds", "review", "shipping", "source", "source_transfer", "statement_descriptor", "statement_descriptor_suffix", "status", "transfer_data", "transfer_group"}, {"id", "object", "amount", "amount_captured", "amount_refunded", "application", "application_fee", "application_fee_amount", "balance_transaction", "billing_details", "calculated_statement_descriptor", "captured", "created", "currency", "customer", "description", "destination", "dispute", "disputed", "failure_balance_transaction", "failure_code", "failure_message", "fraud_details", "invoice", "livemode", "metadata", "on_behalf_of", "order", "outcome", "paid", "payment_intent", "payment_method", "payment_method_details", "receipt_email", "receipt_number", "receipt_url", "refunded", "refunds", "review", "shipping", "source", "source_transfer", "statement_descriptor", "statement_descriptor_suffix", "status", "transfer_data", "transfer_group"})
in
    #"Expanded data1"

This includes steps to convert the API results into a table and extract the data but if you just ant the API responses use this, the result will be a list

let
    Limit = "6",    

    APICall = 

        List.Generate
        (  
            () => [ APIData = Json.Document(Web.Contents("https://api.stripe.com/v1/charges?limit="&Limit)) , MoreData = APIData[has_more] , starting_after = APIData[data]{List.Count(APIData[data]) - 1}[id] ],

            each [MoreData] = true,
           
            each [ APIData = Json.Document(Web.Contents("https://api.stripe.com/v1/charges?limit="&Limit&"&starting_after="&[starting_after])) , MoreData = [APIData][has_more] ,  starting_after = APIData[data]{List.Count([APIData][data]) - 1}[id] ],

            each [APIData]
        )
in
    APICall

I wasn't sure if you were specifying a limit when you called the API but I made provision for that.  If you aren't using a limit parameter then remove the limit="&Limit parts of the the API calls.

I've tested this code on my own Stripe test data and it works correctly.

The way it works is by using List.Generate to create a list containing the data returned by multiple calls to the API.

These are the initial conditions for List.Generate

() => [ APIData = Json.Document(Web.Contents("https://api.stripe.com/v1/charges?limit="&Limit)) , MoreData = APIData[has_more] , starting_after = APIData[data]{List.Count(APIData[data]) - 1}[id] ],

APIData holds the response from the 1st call to the API

MoreData checks for has_more

starting_after checks for the last record id in the returned data.  This is used to tell the API the next record to return

This tells List.Generate to keep going until has_more is false.  You culd just use each [MoreData] but ading the = true bit might make it clearer what's happening, to anyone reading the code

each [MoreData] = true,

for each run through the List.Generate loop

each [ APIData = Json.Document(Web.Contents("https://api.stripe.com/v1/charges?limit="&Limit&"&starting_after="&[starting_after])) , MoreData = [APIData][has_more] , starting_after = APIData[data]{List.Count([APIData][data]) - 1}[id] ],

- Call the API specifying the next record to start retrieval from (inicated by the record id in starting_after)

- Store the new value for has_more in MoreData

- Store the new id of the last record returned in starting_after

Add the data in APIData to the list (the result returned by List.Generate)

each [APIData]

Regards

Phil