Showing results for 
Search instead for 
Did you mean: 
ugurgulluev Regular Visitor
Regular Visitor

Dynamic API call in Query

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:



    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)
    #"Converted to Table" = Record.ToTable(Query1)
    #"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:


    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)])

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



Community Support Team
Community Support Team

Re: Dynamic API call in Query

hi, @ugurgulluev 

You may refer to this post:


Best Regards,



Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.