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
Violet_GA
Frequent Visitor

List.Generate cursor pagination using created.gte Stripe parameter

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

3 REPLIES 3
LearnPowerBI01
Frequent Visitor

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
Frequent Visitor

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
Community Support
Community Support

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.           

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors