cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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:

 

 

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

 

 

1 REPLY 1
Community Support Team
Community Support Team

Re: Dynamic API call in Query

hi, @ugurgulluev 

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-Fu...

 

Best Regards,

Lin

 

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.