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

[rsderby68]

Hello All,

I want to pull our Stripe data into Power BI via their REST API.  However, their pagination method differs from any other API I have worked with.  Normally I build a list table based on some total records field and records per page.  Stripe uses limit (page limit = 100 max), starting_after, and ending_before (see info, below) for object_id field. There is also a has_more (True/False) field that lets you know if there are more records. 

I could use help creating the proper Power Query/M statement to collect all the data.  Would anyone give me a suggestion?  Still pretty new to M. 

This is the endpoint I am using to list all charges:

https://api.stripe.com/v1/charges

Stripe Pagination

All top-level API resources have support for bulk fetches via "list" API methods. For instance, you can list charges, list customers, and list invoices. These list API methods share a common structure, taking at least these three parameters: limit, starting_after, and ending_before.

The response of a list API method represents a single page in a reverse chronological stream of objects. If you do not specify starting_after or ending_before, you will receive the first page of this stream, containing the newest objects. You can specify starting_after equal to the object ID value (see below) of an item to retrieve the page of older objects occurring immediately after the named object in the reverse chronological stream. Similarly, you can specifyending_before to receive a page of newer objects occurring immediately before the named object in the stream. Objects in a page always appear in reverse chronological order. Only one of starting_after or ending_before may be used.

has_more (Boolean)

Whether or not there are more elements available after this set. If false, this set comprises the end of the list.

[PhilipTreacy · Accepted solution]

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

[Anonymous]

Thanks, this worked like a charm. However this creates the Dynamic Data Source error when refreshing from the web. Any workarounds to avoid this?

[Anonymous]

I'll reply my own question in case someone else is struggling:

let

    #"Charges v2" = let

    Limit = "6",    

    Host = "https://api.stripe.com/v1",

 

    APICall =

 

        List.Generate

        (  

            () => [ APIData = Json.Document(Web.Contents(

                Host,

                [RelativePath = "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(Host,

            [RelativePath = "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"}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each #datetime(1970,1,1,0,0,0) + #duration(0,0,0,[created])),

    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type datetime}}),

    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Custom", "Date"}})

in

    #"Expanded data1",

    #"Added Custom" = Table.AddColumn(#"Charges v2", "Date", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [created])),

    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Date", type date}}),

    #"Expanded metadata" = Table.ExpandRecordColumn(#"Changed Type", "metadata", {"component", "firstname", "itemid", "lastname", "paymentarea", "userid", "username"}, {"metadata.component", "metadata.firstname", "metadata.itemid", "metadata.lastname", "metadata.paymentarea", "metadata.userid", "metadata.username"})

in

    #"Expanded metadata"

[Violet_GA]

Hi Phil @PhilipTreacy 

I am trying to modify your code to use created.gt instead of starting_after parameter in order to fetch Stripe data created after 01.01.2023 (Timestamp1672531200). So far I manageed to get only the records within the provided limit but the result says has_more is True. Are you able to assist with some guidelines how to list generate from stripe api all charges created after 01.01.2023?

thank you,

Violet

[rsderby68]

Phil---perfect!  You, indeed, are a super user.  I name you Power Query Jedi Master.  

Best, 

Robert 

[PhilipTreacy]

LOL @rsderby68 

No worries, glad to help.

Phil

[Kalyankumar]

I have mostly the same requirement .  But I don't know where to update the Query .  can you help / guide me

[PhilipTreacy]

Hi @Kalyankumar 

Please start a new topic and include your file and describe the problem there.

Regards

Phil