none
How to automatically retry when error encountered? (X rows loaded. X errors.) RRS feed

  • Question

  • Hello,

    I am using the function below which goes through thousands of rows to return data. When refreshing the data, I often get notified about errors (e.g. 1 errors, 4 errors, 8 errors, etc.) but if I do multiple refreshes at some point I'll have 0 errors. The source data is not changing and from what I can tell the result is that those number of errors equal cells that don't have data populated. As it takes about 1 minute per 200 rows, it can start becoming painful really fast having to do multiple refreshes just to try and get a clean refresh with 0 errors.

    From what I've read the default http timeout value is 100 seconds however based on my refresh duration (with errors) it would not seem I'm timing out. Is there something I could add to the below code that would say to retry X times if what Power Query would consider an error is returned?


    (currentProject as text, MyFilter as text) =>

    let
            url = "https://mydomain.com/rest/search?Project%20=%20" & currentProject & "%20" & MyFilter & "&max=10",
            auth = [Headers=[Authorization="Basic <removed>"], IsRetry = true],
            Source = Json.Document(Web.Contents(url,auth)),

            #"Converted to Table" = Record.ToTable(Source),
            value1 = #"Converted to Table"{2}[Value]
    in
        value1

    Thank you
    Saturday, October 28, 2017 10:07 PM

Answers

  • Hi Miguel,

    In reviewing the network traffic today it would appear to confirm that the code (which I presumed to be this piece - each fnQueryWeb(currentProject, [MyFilter])) is sequentially going through each row making the individual URL calls. Today I also went ahead and removed the Authorization header piece of code to have Excel prompt me for the credentials. The first time I ran the query after that (without capturing the network traffic) I received one error. With each subsequent test after that while capturing network traffic, I haven't received any errors (the query still takes the same amount of time). Unfortunately I do not have access to the server-side of things thus all troubleshooting at this point is client-side.

    As far as your questions:

    API - For the type of queries and desired output I require, individual URL calls seem to be my only option at this time (all that is returned is a single numeric value which is populated into one row associated with the specific query - each query is unique).

    List.Accumulate - This is so that a table with rows of values can be iterated through which Ehren @MS assisted with in the recent forum posting "How to "for each" from table to populate data associated with function call within same query." The "currentProject" represents each row in one table that is iterated through while the "MyFilter" is one of about 1,000 rows in a different table. So with an example of 2 rows in "currentProject" I would end up with a result of 2,000 rows and 2 added columns, each with their corresponding "currentProject" value.


    Thank you
    Sunday, October 29, 2017 11:51 PM

All replies

  • Hey Chaskel,

    I'd suggest that you monitor your requests using something like Fiddler to see why you're getting errors from the source. I'm still confused on why you're getting those errors. Are you getting a 200 response on every call that you make? or you making multiple calls at the same time and getting throttled and perhaps timed out by the actual web service that you're querying? if that's the case then the best bet would be to use a function like Function.InvokeAfter as suggested on this post.

    Something to take in consideration is that you don't want to end up in an infinite loop because of a try error statement.

    Sunday, October 29, 2017 12:46 AM
  • Hi Miguel,

    Thank you for your reply. As of right now I'm not sure if I am getting a 200 or something else so I'll see if I can filter in Wireshark and if that doesn't work out I'll go ahead and install Fiddler. As far as your question if I'm making multiple calls at the same time, the query is the following which I believe when used with the function in my first message is calling one at a time but if it doesn't look that way to you please let me know:

    let
        Source = Table.Buffer(Excel.CurrentWorkbook(){[Name="Filters"]}[Content]),
        DateToday = Table.AddColumn(Source, "Refresh Date", each DateTime.Date(DateTime.LocalNow())),
        Custom1 = List.Accumulate(Projects[MyProject], DateToday, (myTable, currentProject) => Table.AddColumn(myTable, currentProject, each fnQueryWeb(currentProject, [MyFilter]))),
        Custom2 = Table.AddColumn(Custom1, "Sum", each List.Sum(Record.FieldValues(Record.SelectFields(_, Projects[MyProject]))))
    in
        Custom2

    As far as the Function.InvokeAfter, I was actually looking at that on Chris Webb's Blog but I interpreted it as only being able to specify seconds as the minimum which would make the query (which already feels like it takes too long) take too much time when going through thousands of queries. My hope was I could specify a retry of X time only when error is detected to prevent the infinite loop case.

    Thank you

    Sunday, October 29, 2017 2:10 AM
  • Miguel I have one other thought... Would it perhaps speed things up (and perhaps eliminate the error if it ends up being authentication related) if each query didn't have the authentication piece in the function? If so, what would be another way of doing the authentication? I'd actually prefer to not have the base64 user/pass information in the function code anyway but as of now still need to provide a user/pass using some method as opposed to a token/key, etc.

    Thanks again.


    • Edited by Chaskel Sunday, October 29, 2017 2:54 AM
    Sunday, October 29, 2017 2:19 AM
  • Hey Chaskel,

    To be frank, I wouldn't be able to tell you by just seeing your code. You need to check if you're getting 200 responses or if perhaps you're getting some sort 400 or 500 error message at some point against your web service. To be more precise, you need to find out if the error is happening at your web service or if it's somehow happening because of the internals of Power Query.

    My best guess is that somehow the web service is giving you a 400 response for some reason. My other question is how many calls you're making and if somehow you can group them to only make 1 BIG call to the web service, but this relies heavily on reading the documentation of your API or web service and seeing how everything ties together with the data that you want to query.

    EDIT: also, why the List.Accumulate? I'd try to have the values that I'll be using to query the webservice already calculated and not dynamically calculated right at the same time as the webservice as that might cause some unnecessary issues if they rely on some other queries or steps.

    Sunday, October 29, 2017 9:39 AM
  • Hi Miguel,

    In reviewing the network traffic today it would appear to confirm that the code (which I presumed to be this piece - each fnQueryWeb(currentProject, [MyFilter])) is sequentially going through each row making the individual URL calls. Today I also went ahead and removed the Authorization header piece of code to have Excel prompt me for the credentials. The first time I ran the query after that (without capturing the network traffic) I received one error. With each subsequent test after that while capturing network traffic, I haven't received any errors (the query still takes the same amount of time). Unfortunately I do not have access to the server-side of things thus all troubleshooting at this point is client-side.

    As far as your questions:

    API - For the type of queries and desired output I require, individual URL calls seem to be my only option at this time (all that is returned is a single numeric value which is populated into one row associated with the specific query - each query is unique).

    List.Accumulate - This is so that a table with rows of values can be iterated through which Ehren @MS assisted with in the recent forum posting "How to "for each" from table to populate data associated with function call within same query." The "currentProject" represents each row in one table that is iterated through while the "MyFilter" is one of about 1,000 rows in a different table. So with an example of 2 rows in "currentProject" I would end up with a result of 2,000 rows and 2 added columns, each with their corresponding "currentProject" value.


    Thank you
    Sunday, October 29, 2017 11:51 PM
  • glad to hear that you're no longer receiving errors. The most important part is that your calls are getting 200 response codes. If you get any errors from Power Query, then more troubleshooting would be necessary at the PQ level.
    Monday, October 30, 2017 12:56 AM