Styjun

I'm experiencing an issue connecting to the Mailchimp API through Power BI. I had a query that worked fine two weeks ago, but when I loaded the dashboard and updated the data this week, it crashed.

The error is the following:

PowerBI error message

My user interface is set to Spanish, but the message translates to "Error of OLE DB or ODBC: [Expression.Error] The field 'list_stats' was not found on the registry."

I have read in the Mailchimp API documentation that v1.1 to 1.3 of the API is about to be disconnected, but my query connects to the API 2.0 by default, so I guess this is not the issue.

I have tried to remove the said 'list_stats' column from the query, but then I get the error message: 'Microsoft.Mashup.Engine.Interface.ResourceAccessForbiddenException'

This is the code:

let
Origen = MailChimp.TablesV2(),
campaigns = Origen[Key="campaigns"][Data],
#"Tipo cambiado" = Table.TransformColumnTypes(campaigns,"Name", type text, "Key", type text, "Data", type any, "ItemKind", type text, "ItemName", type text, "IsLeaf", type logical),
#"Se expandió Data" = Table.ExpandTableColumn(#"Tipo cambiado", "Data", "id", "campaign_title", "type", "list_id", "list_is_active", "list_name", "subject_line", "preview_text", "emails_sent", "abuse_reports", "unsubscribed", "send_time", "bounces", "forwards", "opens", "clicks", "facebook_likes", "list_stats", "timeseries", "ecommerce", "delivery_status", "_links", "campaignId", "message", "Data.id", "Data.campaign_title", "Data.type", "Data.list_id", "Data.list_is_active", "Data.list_name", "Data.subject_line", "Data.preview_text", "Data.emails_sent", "Data.abuse_reports", "Data.unsubscribed", "Data.send_time", "Data.bounces", "Data.forwards", "Data.opens", "Data.clicks", "Data.facebook_likes", "Data.list_stats", "Data.timeseries", "Data.ecommerce", "Data.delivery_status", "Data._links", "Data.campaignId", "Data.message")
in
 #"Se expandió Data"

In my original query there were more steps formatting the data, which seems to be causing the issue as far I don't get the AccessForbidden error. This is the original code of the query:

let
Origen = MailChimp.TablesV2(),
campaigns = Origen[Key="campaigns"][Data],
#"Tipo cambiado" = Table.TransformColumnTypes(campaigns,"Name", type text, "Key", type text, "Data", type any, "ItemKind", type text, "ItemName", type text, "IsLeaf", type logical),
#"Se expandió Data" = Table.ExpandTableColumn(#"Tipo cambiado", "Data", "id", "campaign_title", "type", "list_id", "list_is_active", "list_name", "subject_line", "preview_text", "emails_sent", "abuse_reports", "unsubscribed", "send_time", "bounces", "forwards", "opens", "clicks", "facebook_likes", "timeseries", "ecommerce", "delivery_status", "_links", "campaignId", "message", "Data.id", "Data.campaign_title", "Data.type", "Data.list_id", "Data.list_is_active", "Data.list_name", "Data.subject_line", "Data.preview_text", "Data.emails_sent", "Data.abuse_reports", "Data.unsubscribed", "Data.send_time", "Data.bounces", "Data.forwards", "Data.opens", "Data.clicks", "Data.facebook_likes", "Data.timeseries", "Data.ecommerce", "Data.delivery_status", "Data._links", "Data.campaignId", "Data.message"),
#"Se expandió Data.bounces" = Table.ExpandRecordColumn(#"Se expandió Data", "Data.bounces", "hard_bounces", "soft_bounces", "syntax_errors", "Data.bounces.hard_bounces", "Data.bounces.soft_bounces", "Data.bounces.syntax_errors"),
#"Se expandió Data.opens" = Table.ExpandRecordColumn(#"Se expandió Data.bounces", "Data.opens", "opens_total", "unique_opens", "open_rate", "last_open", "Data.opens.opens_total", "Data.opens.unique_opens", "Data.opens.open_rate", "Data.opens.last_open"),
#"Se expandió Data.clicks" = Table.ExpandRecordColumn(#"Se expandió Data.opens", "Data.clicks", "clicks_total", "unique_clicks", "unique_subscriber_clicks", "click_rate", "last_click", "Data.clicks.clicks_total", "Data.clicks.unique_clicks", "Data.clicks.unique_subscriber_clicks", "Data.clicks.click_rate", "Data.clicks.last_click"),
#"Tipo cambiado1" = Table.TransformColumnTypes(#"Se expandió Data.clicks","Data.emails_sent", Int64.Type, "Data.abuse_reports", Int64.Type, "Data.unsubscribed", Int64.Type, "Data.send_time", type datetime, "Data.bounces.hard_bounces", Int64.Type, "Data.bounces.soft_bounces", Int64.Type, "Data.bounces.syntax_errors", Int64.Type, "Data.opens.opens_total", Int64.Type, "Data.opens.unique_opens", Int64.Type, "Data.opens.open_rate", Percentage.Type, "Data.opens.last_open", type datetime, "Data.clicks.clicks_total", Int64.Type, "Data.clicks.unique_clicks", Int64.Type, "Data.clicks.unique_subscriber_clicks", Int64.Type, "Data.clicks.click_rate", Percentage.Type, "Data.clicks.last_click", type datetime),
#"Columnas con nombre cambiado" = Table.RenameColumns(#"Tipo cambiado1","Data.type", "type", "Data.list_id", "list_id", "Data.list_is_active", "list_is_active", "Data.list_name", "list_name", "Data.subject_line", "subject_line", "Data.preview_text", "preview_text", "Data.emails_sent", "emails_sent", "Data.abuse_reports", "abuse_reports", "Data.unsubscribed", "unsubscribed", "Data.send_time", "send_time", "Data.bounces.hard_bounces", "bounces.hard_bounces", "Data.bounces.soft_bounces", "bounces.soft_bounces", "Data.bounces.syntax_errors", "bounces.syntax_errors", "Data.opens.opens_total", "opens.opens_total", "Data.opens.unique_opens", "opens.unique_opens", "Data.opens.open_rate", "opens.open_rate", "Data.opens.last_open", "opens.last_open", "Data.clicks.clicks_total", "clicks.clicks_total", "Data.clicks.unique_clicks", "clicks.unique_clicks", "Data.clicks.unique_subscriber_clicks", "clicks.unique_subscriber_clicks", "Data.clicks.click_rate", "clicks.click_rate", "Data.clicks.last_click", "clicks.last_click")
in
 #"Columnas con nombre cambiado"

Any ideas how can I get through this? Thank you.