cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kylekerrigan
Frequent Visitor

Mailchimp API; URL with Dynamic ID

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.

example1.png"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.

example2.png

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

example3.png example4.png

 

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

1 ACCEPTED SOLUTION
mahoneypat
Super User
Super User

Please see this video that covers this scenario.

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

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

2 REPLIES 2
mahoneypat
Super User
Super User

Please see this video that covers this scenario.

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

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

@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  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) example5.png (2)example6.png

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors