List.Generate cursor pagination using created.gte Stripe parameter

Hi Violet_GA ,

The error message "Web.Contents failed to get contents from 'https://api.stripe.com/v1/charges?limit=100&created.gte=2023-05-01' (400): Bad Request" indicates that the request you are making to the Stripe API is not valid.

One possible reason for this error is that the date format you are using in the URL is not correct. You can try using the ISO 8601 format for the date, which is "YYYY-MM-DDTHH:MM:SSZ". For example, "2023-05-01T00:00:00Z" instead of "2023-05-01".

Another possible reason for this error is that the API key you are using to authenticate the request is not valid. You can check if the API key is correct and has the necessary permissions to access the charges data.

Best Regards,

Stephen Tao

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

I have altered the function a bit, though there is an error there is a list with 100 records displayed in power query. The problem is that this function doesn't loop to provide more lists with limit of 100 records, do anyone have an idea why?

let

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

each [MoreData]=true,

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

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

Hello, just wondering if you were able to resolve this? I have a similar requirement where I need to paginate based on last updated_at DateTime value, while calling an api. The api has a limit of 100 records per call, and I'm unable to figure out how to pass the last record's updated_at to the next call in the iteration, so not able to loop through all the records. Any help is appreciated