I managed to get this far getting a table that shows product IDs and customers:
let
Host = "https://api.stripe.com/v1",
Source = Table.TransformColumns(Sessions, {"data.id", each Json.Document(Web.Contents(Host,
[RelativePath = "checkout/sessions/" & _ & "/line_items"]
)), type any}),
#"Expanded data.id" = Table.ExpandRecordColumn(Source, "data.id", {"object", "data", "has_more", "url"}, {"data.id.object", "data.id.data", "data.id.has_more", "data.id.url"}),
#"Expanded data.id.data" = Table.ExpandListColumn(#"Expanded data.id", "data.id.data"),
#"Expanded data.id.data1" = Table.ExpandRecordColumn(#"Expanded data.id.data", "data.id.data", {"description"}, {"data.id.data.description"})
in
#"Expanded data.id.data1"
The proble with this code is that it'll only show the default limit of rows (10). I found this other way to bypass the limit, but it's not showing me the columns that I need:
let
Limit = "6",
Host = "https://api.stripe.com/v1",
APICall =
List.Generate
(
() => [ APIData = Json.Document(Web.Contents(Host,
[RelativePath = "checkout/sessions?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 = "checkout/sessions?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", "after_expiration", "allow_promotion_codes", "amount_subtotal", "amount_total", "automatic_tax", "billing_address_collection", "cancel_url", "client_reference_id", "consent", "consent_collection", "created", "currency", "currency_conversion", "custom_fields", "custom_text", "customer", "customer_creation", "customer_details", "customer_email", "expires_at", "invoice", "invoice_creation", "livemode", "locale", "metadata", "mode", "payment_intent", "payment_link", "payment_method_collection", "payment_method_options", "payment_method_types", "payment_status", "phone_number_collection", "recovered_from", "setup_intent", "shipping_address_collection", "shipping_cost", "shipping_details", "shipping_options", "status", "submit_type", "subscription", "success_url", "total_details", "url"}, {"data.id", "data.object", "data.after_expiration", "data.allow_promotion_codes", "data.amount_subtotal", "data.amount_total", "data.automatic_tax", "data.billing_address_collection", "data.cancel_url", "data.client_reference_id", "data.consent", "data.consent_collection", "data.created", "data.currency", "data.currency_conversion", "data.custom_fields", "data.custom_text", "data.customer", "data.customer_creation", "data.customer_details", "data.customer_email", "data.expires_at", "data.invoice", "data.invoice_creation", "data.livemode", "data.locale", "data.metadata", "data.mode", "data.payment_intent", "data.payment_link", "data.payment_method_collection", "data.payment_method_options", "data.payment_method_types", "data.payment_status", "data.phone_number_collection", "data.recovered_from", "data.setup_intent", "data.shipping_address_collection", "data.shipping_cost", "data.shipping_details", "data.shipping_options", "data.status", "data.submit_type", "data.subscription", "data.success_url", "data.total_details", "data.url"}),
#"Expanded data.custom_fields" = Table.ExpandListColumn(#"Expanded data1", "data.custom_fields"),
#"Expanded data.customer_details" = Table.ExpandRecordColumn(#"Expanded data.custom_fields", "data.customer_details", {"address", "email", "name", "phone", "tax_exempt", "tax_ids"}, {"data.customer_details.address", "data.customer_details.email", "data.customer_details.name", "data.customer_details.phone", "data.customer_details.tax_exempt", "data.customer_details.tax_ids"}),
#"Expanded data.invoice_creation" = Table.ExpandRecordColumn(#"Expanded data.customer_details", "data.invoice_creation", {"enabled", "invoice_data"}, {"data.invoice_creation.enabled", "data.invoice_creation.invoice_data"}),
#"Expanded data.invoice_creation.invoice_data" = Table.ExpandRecordColumn(#"Expanded data.invoice_creation", "data.invoice_creation.invoice_data", {"account_tax_ids", "custom_fields", "description", "footer", "metadata", "rendering_options"}, {"data.invoice_creation.invoice_data.account_tax_ids", "data.invoice_creation.invoice_data.custom_fields", "data.invoice_creation.invoice_data.description", "data.invoice_creation.invoice_data.footer", "data.invoice_creation.invoice_data.metadata", "data.invoice_creation.invoice_data.rendering_options"}),
#"Expanded data.payment_method_types" = Table.ExpandListColumn(#"Expanded data.invoice_creation.invoice_data", "data.payment_method_types"),
#"Expanded data.shipping_options" = Table.ExpandListColumn(#"Expanded data.payment_method_types", "data.shipping_options"),
#"Expanded data.total_details" = Table.ExpandRecordColumn(#"Expanded data.shipping_options", "data.total_details", {"amount_discount", "amount_shipping", "amount_tax"}, {"data.total_details.amount_discount", "data.total_details.amount_shipping", "data.total_details.amount_tax"}),
#"Expanded data.customer_details.tax_ids" = Table.ExpandListColumn(#"Expanded data.total_details", "data.customer_details.tax_ids"),
#"Expanded data.metadata" = Table.ExpandRecordColumn(#"Expanded data.customer_details.tax_ids", "data.metadata", {"component", "firstname", "itemid", "lastname", "paymentarea", "userid", "username"}, {"data.metadata.component", "data.metadata.firstname", "data.metadata.itemid", "data.metadata.lastname", "data.metadata.paymentarea", "data.metadata.userid", "data.metadata.username"}),
#"Expanded data.payment_method_options" = Table.ExpandRecordColumn(#"Expanded data.metadata", "data.payment_method_options", {"wechat_pay"}, {"data.payment_method_options.wechat_pay"}),
#"Expanded data.phone_number_collection" = Table.ExpandRecordColumn(#"Expanded data.payment_method_options", "data.phone_number_collection", {"enabled"}, {"data.phone_number_collection.enabled"}),
#"Expanded data.automatic_tax" = Table.ExpandRecordColumn(#"Expanded data.phone_number_collection", "data.automatic_tax", {"enabled", "status"}, {"data.automatic_tax.enabled", "data.automatic_tax.status"}),
#"Expanded data.consent_collection" = Table.ExpandRecordColumn(#"Expanded data.automatic_tax", "data.consent_collection", {"promotions", "terms_of_service"}, {"data.consent_collection.promotions", "data.consent_collection.terms_of_service"}),
#"Expanded data.custom_text" = Table.ExpandRecordColumn(#"Expanded data.consent_collection", "data.custom_text", {"shipping_address", "submit", "terms_of_service_acceptance"}, {"data.custom_text.shipping_address", "data.custom_text.submit", "data.custom_text.terms_of_service_acceptance"}),
#"Expanded data.customer_details.address" = Table.ExpandRecordColumn(#"Expanded data.custom_text", "data.customer_details.address", {"city", "country", "line1", "line2", "postal_code", "state"}, {"data.customer_details.address.city", "data.customer_details.address.country", "data.customer_details.address.line1", "data.customer_details.address.line2", "data.customer_details.address.postal_code", "data.customer_details.address.state"})
in
#"Expanded data.customer_details.address"
I've no idea how to merge these two so that the query will show ALL line itmes and bypassing the 10 rows limit.
