Dynamic API call in Query

[Anonymous]

Hi all,

I am trying to create a step-by-step operation btw Power BI and Oracle Eloqua.

The operation requires 3 steps:

1- POST request to Eloqua (creates a URL for export)

2- POST request to Eloqua (creates a sync URL based on the export URL in the first step)

3- GET request to Eloqua (calls the data from the URL in the second step.

The first step, I already completed. Below is the code:

let
    Query1 = let
    strBody = "[{""Text"":""" & Text.Replace(varInputText,"""","'") & """}]",
    body = "{
  ""filter"": ""'{{Activity.Type}}'='EmailOpen'"",
  ""name"": ""Bulk Activity Export - Email Open"",
  ""fields"": {
    ""ActivityId"": ""{{Activity.Id}}"",
    ""ActivityType"": ""{{Activity.Type}}"",
    ""ActivityDate"": ""{{Activity.CreatedAt}}"",
    ""ContactId"": ""{{Activity.Contact.Id}}"",
    ""IpAddress"": ""{{Activity.Field(IpAddress)}}"",
    ""VisitorId"": ""{{Activity.Visitor.Id}}"",
    ""VisitorExternalId"": ""{{Activity.Visitor.ExternalId}}"",
    ""EmailRecipientId"": ""{{Activity.Field(EmailRecipientId)}}"",
    ""AssetType"": ""{{Activity.Asset.Type}}"",
    ""AssetName"": ""{{Activity.Asset.Name}}"",
    ""AssetId"": ""{{Activity.Asset.Id}}"",
    ""SubjectLine"": ""{{Activity.Field(SubjectLine)}}"",
    ""EmailWebLink"": ""{{Activity.Field(EmailWebLink)}}"",
    ""CampaignId"": ""{{Activity.Campaign.Id}}"",
    ""ExternalId"": ""{{Activity.ExternalId}}"",
    ""DeploymentId"": ""{{Activity.Field(EmailDeploymentId)}}"",
    ""EmailSendType"": ""{{Activity.Field(EmailSendType)}}"",
    ""EmailAddress"": ""{{Activity.Field(EmailAddress)}}"",
    ""ContactIdExt"": ""{{Activity.Contact.Field(ContactIDExt)}}""
  }
}",
    Source = Json.Document(Web.Contents("WebURL", [Headers=[Authorization="Basic", #"Content-type"="application/json"], Content=Text.ToBinary(body)])),
    #"Converted to Table" = Record.ToTable(Source)
in
    Source,
    #"Converted to Table" = Record.ToTable(Query1)
in
    #"Converted to Table"

This works just fine and creates the export URL which is someting like "/activities/exports/1111", but I am stuck in the 2nd step.

Here is the code I am trying to use in the second step:

let
    strBody = "[{""Text"":""" & Text.Replace(varInputText,"""","'") & """}]",
    body = "{
  ""syncedInstanceUri"": ""& I should include here the URL (dynamic) from the first step &""
}",
    Sourcetwo = Web.Contents("Web URL", [Headers=[#"Authorization"="Basic", #"Content-type"="application/json"], Content=Text.ToBinary(body)])
in
    Sourcetwo

When I use the hard URL from the first step, the second step also works fine, but I couldn't figure out how to make a dynamic call in the 2nd step.

Can you please support me on this? Executing both steps in a single query would be perfect, but if not seperate queries are also fine as long as they are dynamic.

Best regards,

Ugur Gulluev

[v-lili6-msft]

hi, @Anonymous 

You may refer to this post:

https://community.powerbi.com/t5/Desktop/Dynamic-Nested-API-Calls/td-p/250267

https://community.powerbi.com/t5/Community-Blog/Turning-quot-Web-by-Example-quot-into-Power-Query-Functions/ba-p/619941

Best Regards,

Lin