Putting a pause between queries

[ugurgulluev]

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

[Nolock]

Hi @ugurgulluev,

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.

[ugurgulluev]

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]

Hi @ugurgulluev.,

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"

[ugurgulluev]

Thanks @Nolock , I updated the query accordingly but it didn't work.

I am still getting the error: Expression.Error: The field 'items' of the record wasn't found.
Details:
totalResults=0
limit=5000
offset=0
count=0
hasMore=FALSE

Here is the query I used (URLs have been removed for confidentiality reasons):

let
    strBody = "[{""Text"":""" & Text.Replace(varInputText,"""","'") & """}]",
    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&
    "}",
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)),
    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"

Any suggestions? 

Thanks,

Ugur

[Nolock · Accepted solution]

Hi @ugurgulluev,

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]

[ugurgulluev]

Hello @Nolock,

Thank you very much, the code worked!

I am having an issue though in the same query, maybe you can help.

let
    uri1 = "/activities/exports/51485",
    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,1)),
    uri2 = Ssource[uri],
WebPageSourceFunction = () => Json.Document(Web.Contents(""&uri2&"/data?limit=50", [Headers=[Authorization="Basic "]])),
Sasource = Function.InvokeAfter (WebPageSourceFunction, #duration(0,0,0,1)),
    items = Sasource[items],
    #"Converted to Table" = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore),
    #"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"}, {"ActivityId", "ActivityType", "ActivityDate", "ContactId", "IpAddress", "VisitorId", "VisitorExternalId", "EmailRecipientId", "AssetType", "AssetName", "AssetId", "SubjectLine", "EmailWebLink", "CampaignId", "ExternalId", "DeploymentId", "EmailSendType", "EmailAddress", "ContactIdExt"})
in
    #"Expanded Column1"

When I do this, the data is generated, and I am able to see the data in the query as below:

But When I try to apply the query, I get the following error:

What do you think causes this?

Thanks for your valuable support.

Ugur

[Nolock]

Hi @ugurgulluev,

in PowerQuery editor, you can see only the first 1000 rows. It seems to work for all of them. But when you apply, PowerBI loads the entire dataset and there is one or more records which don't have the property items.

What I can recommend: In the left bottom corner in PowerQuery editor, there is a button for changing the behavior, see the screenshots. Change it to Column profiling based on entire data set and check errors.

And then filter out all good results and remain only errors.

[ugurgulluev]

Hi @Nolock,

Tried this but didn't work. I think it's something related to converting JSON process.

Because when I get the first response, it's this (if I apply the query in this step, no issues):

But if I click on the "list" and convert it to a table to get the data table, it gives the error error. I believe it applies the query after converting to table, and then unable to find in this step Source[items].

However, I couldn't stilll figure out how to fix..

Best regards,

Ugur

[Nolock]

Hi @ugurgulluev,

jop, you're right. PowerQuery is a functional language and therefore the code isn't execute line by line but starts at the end after IN operator and lazy evaluates everything what has to be done to get the result.

Ask for Source[items] in IN and then filter for errors.