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

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

rsderby68 — Mon, 07 Nov 2022 17:45:56 GMT

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.

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

PhilipTreacy — Tue, 08 Nov 2022 05:37:05 GMT

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

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

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

rsderby68 — Tue, 08 Nov 2022 15:34:45 GMT

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

Best,

Robert

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

PhilipTreacy — Tue, 08 Nov 2022 22:39:40 GMT

LOL rsderby68

No worries, glad to help.

Phil

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

Violet_GA — Thu, 01 Jun 2023 20:58:56 GMT

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

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

Anonymous — Wed, 27 Sep 2023 08:46:46 GMT

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

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

Anonymous — Wed, 27 Sep 2023 10:24:28 GMT

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"

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

Kalyankumar — Thu, 21 Dec 2023 07:28:40 GMT

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

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

PhilipTreacy — Mon, 01 Jan 2024 09:51:16 GMT

Hi Kalyankumar

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

Regards

Phil

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

p_g_veiga — Mon, 28 Apr 2025 09:01:08 GMT

Hi all, I am stucked trying to solve this connection from PBI to stripe using an API. I am using the following code for events:

= let
Limit = "6",
APICall = List.Generate(
() => [
APIData = Json.Document(Web.Contents("https://api.stripe.com/v1/events?type=charge.succeeded&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/events?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", {
// List your columns here
"id","object","amount","amount_captured","amount_refunded","application","application_fee","application_fee_amount","balance_transaction","billing_details.address.city","billing_details.address.country","billing_details.address.line1","billing_details.address.line2","billing_details.address.postal_code","billing_details.address.state","billing_details.email","billing_details.name","billing_details.phone","calculated_statement_descriptor","captured","created","currency","customer","description","destination","dispute","disputed","failure_balance_transaction","failure_code","failure_message","invoice","livemode","metaClientEmail","metaClientFirstName","metaClientLastName","metaNo. of Cat (Essential) Plans","metaNo. of Cat (Plus) Plans","metaNo. of Cat Plans","metaNo. of Dog (Essential) Plans","metaNo. of Dog (Plus) Plans","metaNo. of Dog Plans","metaNo. of Rabbit (Essential) Plans","metaNo. of Rabbit (Plus) Plans","metaNo. of Rabbit Plans","metaOrderId","metaPHCWelcomePack","metaPracticeName","metaReceivePromosAndMarketing","metaRegisteredAtPractice","metaSurgeryID","on_behalf_of","order","outcome.advice_code","outcome.network_advice_code","outcome.network_decline_code","outcome.network_status","outcome.reason","outcome.risk_level","outcome.seller_message","outcome.type","paid","payment_intent","payment_method","payment_method_details.card.amount_authorized","payment_method_details.card.authorization_code","payment_method_details.card.brand","payment_method_details.card.checks.address_line1_check","payment_method_details.card.checks.address_postal_code_check","payment_method_details.card.checks.cvc_check","payment_method_details.card.country","payment_method_details.card.exp_month","payment_method_details.card.exp_year","payment_method_details.card.extended_authorization.status","payment_method_details.card.fingerprint","payment_method_details.card.funding","payment_method_details.card.incremental_authorization.status","payment_method_details.card.installments","payment_method_details.card.last4","payment_method_details.card.mandate","payment_method_details.card.multicapture.status","payment_method_details.card.network","payment_method_details.card.network_token.used","payment_method_details.card.network_transaction_id","payment_method_details.card.overcapture.maximum_amount_capturable","payment_method_details.card.overcapture.status","payment_method_details.card.regulated_status","payment_method_details.card.three_d_secure","payment_method_details.card.wallet","payment_method_details.type","receipt_email","receipt_number","receipt_url","refunded","refunds.object","refunds.data","refunds.has_more","refunds.total_count","refunds.url","review","shipping","source","source_transfer","statement_descriptor","statement_descriptor_suffix","status","transfer_data","transfer_group","has_more","url","date_new"
})
in
#"Expanded data1"

and the outcome is the one from the below image for all above columns:

Can you guys help me to overcome this limitation. Just to let you know that this same code for charge works 100% but not for events.