Power query does not always update the field in the table.

Question

I run power queries from macros to update Excel spreadsheets with new data. I have one query that always works, but with today's dataset, although the query updated completely in the query window, one field in the resulting table in the spreadsheet was left null. All other fields were fine in the spreadsheet. I was able to fix the problem by going back and refreshing the table again by hand. Then the field populated.

Below is a simplified version of the macro I use to run the queries. First, I loop to run all the queries individually. Then I use a command to wait until the queries complete. Then I wait an additional minute to make sure they completed. Then I Refresh all and again wait to make sure the queries completed. But in spite of all that, I still find one query did not complete correctly. I had to go back and refresh by hand.

Since this is part of a series of steps, and this one step fails, it messes up everything that occurs after it.

Is there anything I can do to make sure that the refresh-all from the macro does indeed complete the refresh and updates the whole table?

Is there a simpler way of doing this without adding the timers at the end to make sure the queries finish, preferably with one pass instead of two?

==== Portion of my Macro Code =========================

For Each Connection In ActiveWorkbook.Connections

  If Connection.Type = xlConnectionTypeOLEDB Then

       Connection.OLEDBConnection.BackgroundQuery = False

       On Error GoTo errorhandler

       Connection.Refresh

  End If

Next Connection

Application.CalculateUntilAsyncQueriesDone

Application.Wait (Now + TimeValue("0:01:00"))

' Second Pass

ActiveWorkbook.RefreshAll

Application.CalculateUntilAsyncQueriesDone

Application.Wait (Now + TimeValue("0:02:00"))