Mailchimp API; URL with Dynamic ID

[kylekerrigan]

Longtime forum reader, first-time forum poster… these forums have helped me out so much in the last 2 years.

My objective: create a data model with "Click details" for each "Campaign" by making iterative calls to Mailchimp's API.

Mailchimp's Marketing API documentation prescribes this URL "https://{server}.api.mailchimp.com/3.0/reports/{campaign_id}/click-details?" for obtaining the data I need for each Campaign.

To obtain a list of {campaign_id}, I can use this URL and query: "https://{server}.api.mailchimp.com/3.0/campaigns?count=500&offset=0"

let
Source = Json.Document(Web.Contents("https://{server}.api.mailchimp.com/3.0/campaigns?count=500&offset=0")),
campaigns = Source[campaigns],
#"Converted to Table" = Table.FromList(campaigns, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"ID Table" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id"}, {"campaign_id"})
in
#"ID Table"

My attempts:

1) Referencing this forum post ("How to pass a value from one table iteratively to another API request in power bi"), I used the above query ("ID Table") and tried this:

let
Source = campaign_list_test,
#"CampaignID" = Source[campaign_id],

QueryAPI = (campaign_id)=>

let
Source1 = Json.Document(Web.Contents("https://[server}.api.mailchimp.com/3.0/reports/"& campaign_id & "/click-details?")),
urls_clicked = Source1[urls_clicked]

in
urls_clicked,

AddColumn = Table.AddColumn(Source,"NewColumnAPI", each QueryAPI(#"CampaignID"))
in
AddColumn

// I receive an error.

"Expression.Error: We cannot apply operator & to types Text and List."

2) Referencing this forum post  ("Nested API Calls") and reading the BI Accountant's blog on webscrapping multiple pages, I attempted to transform the query to a function, but I custom function generated the same data as the {campaign_id} referenced in the parameter she prescibes to create. That is, every Table has the same data.

3) Referencing multiple forum posts, I attempted to create a Custom Column to reference the {campaign_id}. No luck.

 

I welcome any help and advice someone can provide. Thanks in advance!

[kylekerrigan]

RM7 -

Thanks for your reply! I figured out the pagination and transformations I would need a little while ago. PBI used to maintain connectors for Mailchimp and other services, but they ended some of those partnerships some time before my original post.

But, your timing impeccable: we purchased a Windsor.ai account just days before your reply, so that we could easily connect to Mailchimp, Meta, Meltwater, etc.  So far, it's been a great service.

Again, thanks for your reply.

KK

[mahoneypat · Accepted solution]

Please see this video that covers this scenario.

(3) Power BI - Tales From The Front - REST APIs - YouTube

Regards,

Pat

[kylekerrigan]

@mahoneypat Thank you.  Excellent video.  Your pagination explanation is helpful, and I've already used it today on another REST API data source that I've struggled to iterate.  I could not get your Invoke Customer Function method to work for my data source; similar to the BI Accountant's approach (referenced above), the custom f  returns the same Table of data for each [campaign_id].  However, your video still led me to the solution.

I found the problem and solved it two ways. I need my [campaign_id] values to be changed to type 'Text'.  (1) I changed it at the top of the column; (2) it worked when I used Text.From([campaign_id]) within the concatenation of the URL.

(1)  (2)