Forum Discussion

gehe_bbe's avatar
gehe_bbe
Frequent Visitor
1 year ago

Some Queries Only Refresh on Second Attempt – How to Force Full Refresh?

Good afternoon guys,

I have a Power Query model in Excel that pulls data from an SQL database. Since different customers use this tool, I ship it with a small dummy dataset. For this, I use Excel tables that are structurally identical to the SQL data but contain only a few rows. The relationships in the dummy data model are correctly set up and functional. As a potential solution, I tried modifying the dummy mode to select only the first few rows from the SQL tables instead—but the issue persists.

 

The Issue:

After switching between dummy data and real data, the first refresh does not update 5 out of 11 queries—they only update on the second refresh. The same 5 queries are always affected. This happens regardless of their size or complexity, as both small and large tables are affected. The remaining 6 queries always load correctly on the first refresh.

All 11 queries are configured with “Include this query in ‘Refresh All’” enabled. I trigger the refresh either manually via “Refresh All” or via VBA using ThisWorkbook.RefreshAll, yet the issue remains.

 

Tested Solutions That Did Not Work:

  • Adding a dynamic parameter (e.g., NOW() or an Excel cell) to the SQL statement to bypass caching
  • Creating a master query to trigger all dependent queries
  • Adding extra columns to force query recognition
  • Running a sequential refresh (which doubles the load time and is not feasible)

 

The queries that fail on the first refresh do not even show the Power Query refresh animation (spinning dots), meaning they are not being processed at all. The behavior ist the same: Switching from Live to Dummy or the other way.

 

Since it always affects the same queries, it seems that Power Query simply ignores these tables on the first refresh. Has anyone encountered a similar issue or found a way to force all queries to refresh on the first attempt?

Thanks for your help and suggestions, Steve

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi gehe_bbe,

    Since you have mentioned that you use VBA to operate on "Refresh ALL", do you mind to refresh second time with a dealy after first refresh?

    Sub RefreshAllTwice()
    ThisWorkbook.RefreshAll
    Application.OnTime Now + TimeValue("00:00:05"), "ThisWorkbook.RefreshAll"
End Sub

     

    Except for that, please check the following tips for reference:

    1). Check the query dependency, Power Query → View → Query Dependencies to see how queries are linked.

    2). In Power Query → Close & Load To..., make sure all queries load into the Data Model or worksheet. If a query is only referenced but not loaded, it might be skipped.

    3). Clear Cache Before Refres, go to File → Options → Data → Clear Cache before refreshing

     

    Best Regards,
    Qi
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

     

    • gehe_bbe's avatar
      gehe_bbe
      Frequent Visitor
      1 year ago

      Hi Qi,

      The VBA Code doubles the time for loading and is therefore not a suitable solution.
      Clearing the Cache hasn't effect something, same behavior like before.

      Thanks, Steve

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi gehe_bbe,

        Well, now I could not reproduce your issue on my side, so I could only provide some tips for your reference.

        RefreshAllTwice does double the time, if you mind the refresh time, it is  not a good choice.

         

        If there is still no improvement after some other digging, you coudl consider contacting our support team:

        How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

         

        Best Regards,
        Qi
        Community Support Team

         

        If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
        If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!