Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
Please see this video that covers this scenario.
(3) Power BI - Tales From The Front - REST APIs - YouTube
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
Please see this video that covers this scenario.
(3) Power BI - Tales From The Front - REST APIs - YouTube
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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)