Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rsderby68
Resolver I
Resolver I

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

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.

1 ACCEPTED SOLUTION
PhilipTreacy
Super User
Super User

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

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

8 REPLIES 8
PhilipTreacy
Super User
Super User

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

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Anonymous
Not applicable

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
Not applicable

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"

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

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

Best, 

Robert 

LOL @rsderby68 

 

No worries, glad to help.

 

Phil

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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

Hi @Kalyankumar 

 

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

 

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors