I'm querying the Stripe API that forces me to have a dynamic content for their endpoint to get the data I need. I managed to find a way doing this by querying another table (checkout sessions), but this created an issue in terms that PowerBI won't automatically refresh if there's dynamic content. Is there a fix for this? On desktop I can simply change privacy levels, but that doesn't seem an option for PowerBI web server:
let
Source1 = Json.Document(Web.Contents("https://api.stripe.com/v1/checkout/sessions")),
Source2 = Table.TransformColumns(sessions, {"data.id", each Json.Document(Web.Contents("https://api.stripe.com/v1/checkout/sessions/" & _ & "/line_items")), type any}),
#"Converted to Table" = Table.FromRecords({Source1}),
#"Expanded data" = Table.ExpandListColumn(#"Converted to Table", "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.automatic_tax" = Table.ExpandRecordColumn(#"Expanded data1", "data.automatic_tax", {"enabled", "status"}, {"data.automatic_tax.enabled", "data.automatic_tax.status"}),
#"Expanded data.custom_text" = Table.ExpandRecordColumn(#"Expanded data.automatic_tax", "data.custom_text", {"shipping_address", "submit"}, {"data.custom_text.shipping_address", "data.custom_text.submit"}),
#"Expanded data.invoice_creation" = Table.ExpandRecordColumn(#"Expanded data.custom_text", "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.invoice_creation.invoice_data.metadata" = Table.ExpandRecordColumn(#"Expanded data.invoice_creation.invoice_data", "data.invoice_creation.invoice_data.metadata", {}, {}),
#"Expanded data.metadata" = Table.ExpandRecordColumn(#"Expanded data.invoice_creation.invoice_data.metadata", "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.payment_method_options.wechat_pay" = Table.ExpandRecordColumn(#"Expanded data.payment_method_options", "data.payment_method_options.wechat_pay", {"app_id", "client"}, {"data.payment_method_options.wechat_pay.app_id", "data.payment_method_options.wechat_pay.client"}),
#"Expanded data.phone_number_collection" = Table.ExpandRecordColumn(#"Expanded data.payment_method_options.wechat_pay", "data.phone_number_collection", {"enabled"}, {"data.phone_number_collection.enabled"}),
#"Expanded data.total_details" = Table.ExpandRecordColumn(#"Expanded data.phone_number_collection", "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.id" = Table.ExpandRecordColumn(Source2, "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"
