Search code examples
sql-serverexcelrefreshpowerquery

Excel 2016 : Right-click --> Refresh in a table does not actually refresh data, from SQL database

According to the official MS website, it states that to Refresh data from a Microsoft Query, the Data Connection Wizard, or Web query, I should click Refresh ALL from the Data tab.

However, if I right-click a table, who's data-source comes from SQL Server (PowerQuery - Source = Sql.Database(), and click Refresh, the data table is simply reloaded from cache.

No actual previous updates in the database are reloaded back to the table. In other words, it doesn't refresh the table at all, but just reloads what it originally had.

Why?

Solution

  • I had to revert to VBA to do a proper refresh, and it worked. However, this is not a proper solution as it's outside the normal Excel right-click options.

    Range("A5").Select
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False