creating a query based off a field in a prior query

[gotmike]

So I'm trying to run a query in Power BI that is based off a field in a prior query.

In this example, I'm running a query on the Campaign Monitor API that grabs all the emails that have been sent:

https://api.createsend.com/api/v3.1/clients/{ClientID}/campaigns.xml

The {ClientId} is static.

This returns a table that includes (among other things) a CampaignID for each row.

I now want to cycle through the results and get the click details for each one, using the CampaignId:

https://api.createsend.com/api/v3.1/campaigns/{CampaignId}/clicks.xml

So... how do I run the first query, then do a "foreach" through the results and use the CampaignId from each query to then run a subsequent query?

Incidentally, I'd also like to produce a result table that has an initial column of CampaignId and then append the results from the follow-up query.

The Advanced Editor shows the following for my initial query:

let
Source = Xml.Tables(Web.Contents("https://api.createsend.com/api/v3.1/clients/{ClientId}/campaigns.xml")),
Table0 = Source{0}[Table],
#"Changed Type" = Table.TransformColumnTypes(Table0,{{"FromEmail", type text}, {"FromName", type text}, {"Name", type text}, {"ReplyTo", type text}, {"SentDate", type datetime}, {"TotalRecipients", Int64.Type}, {"CampaignID", type text}, {"Subject", type text}, {"WebVersionURL", type text}, {"WebVersionTextURL", type text}})
in
#"Changed Type"

[ImkeF]

Having problems understanding your request - also your code is also being cut off (this seems to be a current issue in this forum at the moment).

But from what I understood now is that you might need to merge your 2 queries on CampaignId.

Could you paste a picture of the 2 tables and the desired result?

[gotmike]

Ok, let me know if this clears it up a bit...

I'm getting Query 1 info fine, which populates a table, but I'm not sure how to then grab just one of those fields and do anything with it.

[ImkeF]

You retrieve the column from Query 1 like this: #"Query 1"[CampaignID]

the field from the first row/record of this column like this: #"Query 1"[CampaignID]{0} (zero because M starts to count with 0 instead of 1)

What's missing in your example is how the row from Query 2 would know that is has to connect with the CampaignID from your example. Does it contain the CampaignID as well?

Having the feeling that I'm missing the big picture here.

So your tables probably don't have more than one row... And this is about sth more than a simple lookup?

So sorry, I need more data/examples.

[gotmike]

sorry... here i'll write it in pseudo-code... maybe that's better.

Table1 = Get MyData1 from API(apiKey)

j = 0

For Each RECORD in Table1
{
    // get the CampaignId
    CampaignId = RECORD.Field6
    // use CampaignId to run the next query for more details about this campaign
    Table2 = Get MyData2 from API(apiKey,CampaignId) // note: we know that this returns only one record
    // set the first column value equal to the CampaignId
    Table3[j,0] = CampaignId

    // cycle through the data returned by the API about this specific CampaignId, one field at a time (we only get one record)
    For (i = 1 to Table2.NumberOfColumns)
    {
        // shift columns to the right one in the new table
        // so what was originally in col 0 is now in col 1, and so forth
        // this makes space for the new CampaignId column
        Table3[j,i] = Table2[1,i-1]
    }

    // increment counter
    j++

}

[ImkeF]

Thanks, that's clearer now!

You don't have to write an explicit loop for this behaviour. Just reference your query 1 and select the column with the Campaign ID like this:

let

Source=Table1[CampaignID]

in

Source

Then you add a Custom column that contains what you want to perform for each field of the CampaignID-column. If this is a couple of steps, you either write this as a function or as a nested formular. The more complex the operation - function method will be preferrable.

You will pass the CampaignID to the function like this:

FunctionCall = Table.AddColumn(Source, "FunctionCall", each fnFunction([CampaignID]))

The function could look like this:

fnFunction = (campid) =>

let

Table2 = Get MyData2 from API(apiKey, campid),

AddCampIDCol = Table.AddColumn(Table2, “CampaignID”, each campid)

in

AddCampIDCol

You see in step AddCampIDCol that you simply add this as a column to the result you’ve retrieved from your second pull.

Hope this gives you an idea on how to tackle this in M.

Please let me know if there is more to explain.

[gotmike]

Okay, I know this has been a long while... hahaha.

But I'm STILL struggling with this years later. Admittedly, I'm not in Power BI every day, but I keep coming back and having trouble every time.

Still with Campaign Monitor, I have two tables...

1) Table: `CM_Campaigns` - This has a list of all campaigns I have run. Each record has a Campaign ID.

2) Table: `CM_CampaignSummary` - This is a pivoted table that has a single record. The API call returns JSON for a single campaign. I bring in the JSON, then pivot to turn it into a record.

What I'd like to do is essentially grab the Campaign ID from EACH record of the first table, then run the API call for the second table and append the data to the first table, adding the summary stats to the first table as additional fields. I went back through your above explanation and I'm still not getting it.

Table #1's API call comes from `https://api.createsend.com/api/v3.2/clients/{clientid}/campaigns.json`

And `{clientid}` is static, so no need to change.

The JSON data brought into Table #1 looks something like this. You will notice multiple records are returned.

[
    {
        "FromName": "My Name",
        "FromEmail": "myemail@example.com",
        "ReplyTo": "myemail@example.com",
        "WebVersionURL": "http://createsend.com/t/r-765E86829575EE2C/",
        "WebVersionTextURL": "http://createsend.com/t/r-765E86829575EE2C/t",
        "CampaignID": "fc0ce7105baeaf97f47c99be31d02a91",
        "Subject": "Campaign One",
        "Name": "Campaign One",
        "SentDate": "2010-10-12 12:58:00",
        "TotalRecipients": 2245
    },
    {
        "FromName": "My Name",
        "FromEmail": "myemail@example.com",
        "ReplyTo": "myemail@example.com",
        "WebVersionURL": "http://createsend.com/t/r-DD543566A87C9B8B/",
        "WebVersionTextURL": "http://createsend.com/t/r-DD543566A87C9B8B/t",
        "CampaignID": "072472b88c853ae5dedaeaf549a8d607",
        "Subject": "Campaign Two",
        "Name": "Campaign Two",
        "SentDate": "2010-10-06 16:20:00",
        "TotalRecipients": 11222
    }
]

So, from the above API call, I get a list of `CampaignID` values.

Then, I want to run queries for Table #2, which go to `https://api.createsend.com/api/v3.2/campaigns/{campaignid}/summary.json`

You will notice that I need the `{campaignid}` value for EACH query. I cannot send a batch (I wish I could!)

The JSON data brought into Table #2 looks something like this. You will notice that only a single record is returned.

{
    "Recipients": 1000,
    "TotalOpened": 345,
    "Clicks": 132,
    "Unsubscribed": 43,
    "Bounced": 15,
    "UniqueOpened": 298,
    "SpamComplaints": 23,
    "WebVersionURL": "http://createsend.com/t/y-A1A1A1A1A1A1A1A1A1A1A1A1/",
    "WebVersionTextURL": "http://createsend.com/t/y-A1A1A1A1A1A1A1A1A1A1A1A1/t",
    "WorldviewURL": "http://myclient.createsend.com/reports/wv/y/8WY898U9U98U9U9",
    "Forwards": 18,
    "Likes": 25,
    "Mentions": 11
}

Now, I presume I could write an API "wrapper" in some other language and query that API instead, but I was trying to do this all inside Power BI.

Any help would be much appreciated.

[AnetaK]

Hi Imke,

I want to follow your steps but I've encountered a problem at the very begining.

There is no possibility to create any column after doing this:

let

Source=Table1[CampaignID]

in

Source

[ImkeF]

Hi @AnetaK,

true, you have to transform this list to a table before moving on with the next steps.

[AnetaK]

Thank you 🙂

Could I ask you for more help?

I do not understand all that you've written here.

Would you be so kind and help me with one example?

This is a query that receives all CampaingnID's for client 1a2b3c.

let
Source = Xml.Tables(Web.Contents("https://api.createsend.com/api/v3.1/clients/1a2b3c/campaigns.xml")),
Table0 = Source{0}[Table],
#"Changed Type" = Table.TransformColumnTypes(Table0,{{"FromEmail", type text}, {"FromName", type text}, {"Name", type text}, {"ReplyTo", type text}, {"SentDate", type datetime}, {"TotalRecipients", Int64.Type}, {"CampaignID", type text}, {"Subject", type text}, {"WebVersionURL", type text}, {"WebVersionTextURL", type text}})
in
#"Changed Type"

In the second query I want to receive info about bounces in all campains.

However my query gets data about only one, "1234".

let
Source = Xml.Tables(Web.Contents("https://api.createsend.com/api/v3.1/campaigns/1234/bounces.xml")),
Results = Source{0}[Results],
Bounce = Results{0}[Bounce],
#"Changed Type" = Table.TransformColumnTypes(Bounce,{{"BounceType", type text}, {"EmailAddress", type text}, {"ListID", type text}, {"Date", type datetime}, {"Reason", type text}})
in
#"Changed Type"

How should a function look like to get the bounce information for all campaigns in one table?

I also need a CampainID column.

[ImkeF]

Hi @AnetaK,

I believe this blogpost might help you: https://exceleratorbi.com.au/combine-excel-workbooks-power-query-method-1/