Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Putting a pause between queries

Hello all,

 

I have been working on getting the data from Oracle Eloqua by including POST and GET requests within the same query.

 

The issue I have is that I am running 6 different calls and probably because when I refresh the calls, Power BI makes the requests at the same time (and please beware that each query includes 2 POST and 1 GET request) and the server doesn't respond and I am not able to get the data.

 

There are two things that I need to do to fix this.

 

1- Adding a timeout function within the query, to make sure PowerBI sends a call again after some time until the call is successful.

2- Adding a pause between POST-POST-GET calls to make sure the query doesn't jump into the next step until the previous call is complete.

 

I checked the "Function.InvokeAfter" for this but didn't understand how to use it.

 

Here are the two example queries I use for the calls.

 

let
    today = DateTime.ToText(DateTime.LocalNow(), "yyyy-MM-dd"),
    filt = """filter"":""'{{Activity.Type}}'='EmailOpen'""",
    body = "{
    "&filt&"
    ""name"": "" TEST 2- 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("", [Headers=[Authorization="Basic", #"Content-type"="application/json"], Content=Text.ToBinary(body)])),
    uri1 = Source[uri],
    url ="{
      ""syncedInstanceUri"":"&uri1&
    "}",
Ssource = Json.Document(Web.Contents("", [Headers=[Authorization="Basic", #"Content-type"="application/json"], Content=Text.ToBinary(url)])),
    uri2 = Ssource[uri],
Sasource = Json.Document(Web.Contents("", [Headers=[Authorization="Basic"]])),
    items = Sasource[items],
    #"Converted to Table" = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"ActivityId", "ActivityType", "ActivityDate", "ContactId", "IpAddress", "VisitorId", "VisitorExternalId", "EmailRecipientId", "AssetType", "AssetName", "AssetId", "SubjectLine", "EmailWebLink", "CampaignId", "ExternalId", "DeploymentId", "EmailSendType", "EmailAddress", "ContactIdExt"}, {"Column1.ActivityId", "Column1.ActivityType", "Column1.ActivityDate", "Column1.ContactId", "Column1.IpAddress", "Column1.VisitorId", "Column1.VisitorExternalId", "Column1.EmailRecipientId", "Column1.AssetType", "Column1.AssetName", "Column1.AssetId", "Column1.SubjectLine", "Column1.EmailWebLink", "Column1.CampaignId", "Column1.ExternalId", "Column1.DeploymentId", "Column1.EmailSendType", "Column1.EmailAddress", "Column1.ContactIdExt"})
in
    #"Expanded Column1"
let
    Query1 = let
    today = DateTime.ToText(DateTime.LocalNow(), "yyyy-MM-dd"),
    filt = """filter"":""'{{Activity.Type}}'='Bounceback'""",
    body = "{
    "&filt&"
    ""name"": "" TEST 2- Bulk Activity Export - Bounceback"",
  ""fields"": {
    ""ActivityId"": ""{{Activity.Id}}"",
   ""ActivityType"": ""{{Activity.Type}}"",
    ""ActivityDate"": ""{{Activity.CreatedAt}}"",
    ""ContactId"": ""{{Activity.Contact.Id}}"",
    ""EmailRecipientId"": ""{{Activity.Field(EmailRecipientId)}}"",
    ""AssetType"": ""{{Activity.Asset.Type}}"",
    ""AssetName"": ""{{Activity.Asset.Name}}"",
    ""AssetId"": ""{{Activity.Asset.Id}}"",
    ""CampaignId"": ""{{Activity.Campaign.Id}}"",
    ""ExternalId"": ""{{Activity.ExternalId}}"",
    ""DeploymentId"": ""{{Activity.Field(EmailDeploymentId)}}"",
    ""SmtpErrorCode"": ""{{Activity.Field(SmtpErrorCode)}}"",
    ""SmtpStatusCode"": ""{{Activity.Field(SmtpStatusCode)}}"",
    ""SmtpMessage"": ""{{Activity.Field(SmtpMessage)}}""
  }
}",
Source = Json.Document(Web.Contents("", [Headers=[Authorization="Basic", #"Content-type"="application/json"], Content=Text.ToBinary(body)])),
    uri1 = Source[uri],
    url ="{
      ""syncedInstanceUri"":"&uri1&
    "}",
Ssource = Json.Document(Web.Contents("", [Headers=[Authorization="Basic", #"Content-type"="application/json"], Content=Text.ToBinary(url)])),
    uri2 = Ssource[uri],
Sasource = Json.Document(Web.Contents("", [Headers=[Authorization="Basic"]])),
    items = Sasource[items]
    in
Sasource,
    items = Query1[items],
    #"Converted to Table" = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"ActivityId", "ActivityType", "ActivityDate", "ContactId", "EmailRecipientId", "AssetType", "AssetName", "AssetId", "CampaignId", "ExternalId", "DeploymentId", "SmtpErrorCode", "SmtpStatusCode", "SmtpMessage"}, {"Column1.ActivityId", "Column1.ActivityType", "Column1.ActivityDate", "Column1.ContactId", "Column1.EmailRecipientId", "Column1.AssetType", "Column1.AssetName", "Column1.AssetId", "Column1.CampaignId", "Column1.ExternalId", "Column1.DeploymentId", "Column1.SmtpErrorCode", "Column1.SmtpStatusCode", "Column1.SmtpMessage"})
in
    #"Expanded Column1"

Any help is very much appreciated.

 

Best regards,

Ugur

Need Help
Show and Tell
  • Nolock's avatar
    Nolock
    7 years ago

    Hi Anonymous,

    it looks fine, I don't see any syntax errors. Let's debug it:

     

    let
    Source = Json.Document(Web.Contents("", [Headers=[Authorization="Basic ", #"Content-type"="application/json"], Content=Text.ToBinary(body)])),
    uri1 = Source[uri],
    url ="{
      ""syncedInstanceUri"":"&uri1&
    "}",
WebPageSourceFunc = () => Json.Document(Web.Contents("", [Headers=[Authorization="Basic", #"Content-type"="application/json"], Content=Text.ToBinary(url)])),
Ssource = Function.InvokeAfter (WebPageSourceFunc, #duration(0,0,0,10)),
    uri2 = Ssource[uri],
WebPageSourceFunction = () => Json.Document(Web.Contents(""&uri2&"/data?limit=5000", [Headers=[Authorization="Basic"]])),
Sasource = Function.InvokeAfter (WebPageSourceFunction, #duration(0,0,0,10))
in
    Sasource

    You will get your Sasource as result and you can check what the result is. And after that you can add the next step with Sasource[items] . You can do it also direkt on the last line like: in Sasource[items]

9 Replies

  • Nolock's avatar
    Nolock
    Resident Rockstar
    7 years ago

    Hi Anonymous,

     

    have you tried to create 3 separate sources, load them one after each other, and then merge them together? 

    Parallel loading can be disabled in Setting in Power BI, see the screenshot below.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hello Nolock,

       

      Thanks for the reply.

       

      The sources are connected to each other, first POST request creates a URL for the 2nd post requests, so if I need to work thorugh seperate sources, I need to find a way to implement the dynamic URL from the 1st call to the 2nd.

       

      I tried disabling parallel load, but it didn't work. That shows me that the error is caused since the query is trying to jump to the 2nd POST request before the 1st is completed. I need to use a pause/don't jump to the 2nd step before the first is completed code, but I couldn't figure out how to use this.

       

      Any support is very much appreciated.

       

      Best regards,

      Ugur

      • Nolock's avatar
        Nolock
        Resident Rockstar
        7 years ago

        Hi Anonymous.,

        aha, I see. Then use Function.InvokeAfter as you suggested. I've written an example for you how to call it. At the beginning you have to write a function (in my case WebPageSourceFunc) without parameters and then call the function with Function.InvokeAfter with a duration of e.g. 10 seconds.

        Make all the steps as simple as possible to be able to debug everything. If you had still some difficulties, let me know and I will rewrite your query with the usage of Function.InvokeAfter.

         

        let
    WebPageSourceFunc = () => Web.Page(Web.Contents("https://en.wikipedia.org/wiki/City_of_London")),
    Source = Function.InvokeAfter(WebPageSourceFunc, #duration(0,0,0,10)),
    Data3 = Source{3}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data3,{{"Header", type text}, {"Year", Int64.Type}, {"Pop.", type number}, {"±%", type text}})
in
    #"Changed Type"