List.Generate cursor pagination using created.gte Stripe parameter

[Violet_GA]

Hi All, 

I am trying to fetch charges records from Stripe API using their coursor paginator parameter created.gte, which would source all charges after or on certain created date.

For this I am using this simplified  structure of List.Generate function in power query 

let
UTCTime= [APIData][data][created]/(60*60*24),

StripeData =
List.Generate
(
() => [StripeRecords = Json.Document(Web.Contents("https://api.stripe.com/v1/charges?limit=100&created.gte=" & Date.ToText("2023-05-01"))), MoreData=StripeRecords[has_more]],

each [MoreData]=true,

each [StripeRecords = Json.Document(Web.Contents("https://api.stripe.com/v1/charges?limit=100&created.gte=" & Date.ToText(List.Last([StripeRecords][data])[created], ("1/1/1970")+UTCTime))), MoreData = [APIData][has_more]],

each [StripeRecords]
)
in
StripeData

but I am getting the following error: "An error occurred in the ‘charges’ query. DataSource.Error: Web.Contents failed to get contents from 'https://api.stripe.com/v1/charges?limit=100&created.gte=2023-05-01' (400): Bad Request
Details:
DataSourceKind=Web
DataSourcePath=https://api.stripe.com/v1/charges
Url=https://api.stripe.com/v1/charges?limit=100&created.gte=2023-05-01" does anyone know why and how to overcome it?

thank you,

Violet

[LearnPowerBI01]

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

[Violet_GA]

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"

[v-stephen-msft]

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.