Loop Through Several Values, Change API Call URL and Insert Into Table

[awap87]

Hi,

First of all, sorry if the subject seems a little confusing.

I'm hoping to use the Dotmailer API to obtain some insights on email campaigns that I've sent (without having to login to the Dotmailer portal).

Using the Dotmailer API docs as a guide, I've pulled a complete list of all campaigns using the following call:

https://[region ID]-api.dotmailer.com/v2/campaigns

However, after checking the docs (and speaking to Dotmailer), if I wanted to pull any insights on the campaigns (opens, click through rate etc), this can only be done on a campaign by campaign basis using the following call:

https://[region ID]-api.dotmailer.com/v2/campaigns/[ID]/summary

Now, what I'm hoping to do is take each campaign ID returned from the first call, insert the ID column into a fresh table.

Then, I'd like to loop through each ID in the new table, update the URL for the 2nd call with each of the individual campaign ID's and insert the insights next to the correct campaign number.

Would this be possible at all?

Many thanks

[v-ljerr-msft · Accepted solution]

Hi @awap87,

It could be done. 

First, create a function using syntax like below.

let functionName = (param1 as datatype) =>
let
 Source = Json.Document(Web.Contents("https://[region ID]-api.dotmailer.com/v2/campaigns/" & param1 & "/summary").
in
 Source
in functionName

After you have inserted the ID column in to a new table, then you can add a custom column to the table that calls/invokes the function above. 

Reference:
http://datachix.com/2014/05/22/power-query-functions-some-scenarios/

http://satalyst.com/power-query-creating-a-parameterized-function-in-m/

Regards

[Anonymous]

Hi @v-ljerr-msft,

I do have the similar requirment and was going through theis solution but I didnt understand where to write this function and  from where to call?  Sorry new to this  type of work.

Please advise.

Thanks