如何强制Power Query清除缓存，确保查询完整执行以获取准确性能指标？

我写了个VBA宏，用来运行工作簿里的所有查询并记录每个查询的耗时。第一次测试时功能正常，但之后每次测试耗时都不到一秒，应该是Power Query缓存了查询结果，没像第一次那样执行完整查询。想知道有没有办法强制它每次都从头执行完整查询。

我的VBA代码如下：

For Each conn In ThisWorkbook.Connections
    If InStr(1, conn.Name, "Query - test_") = 1 Then

        Debug.Print "Refreshing " & conn.Name

        connBgStatus = conn.OLEDBConnection.BackgroundQuery '保存当前后台刷新设置
        conn.OLEDBConnection.BackgroundQuery = False '临时禁用后台刷新

        startTime = Timer
        conn.Refresh
        endTime = Timer

        conn.OLEDBConnection.BackgroundQuery = connBgStatus '恢复原后台刷新设置

        returnArr(i, 0) = testID & "_" & Format(i + 1, "000")
        returnArr(i, 1) = conn.Name
        returnArr(i, 2) = endTime - startTime

        i = i + 1
    End If
Next

当前输出情况：首次刷新时各查询耗时在10-15秒左右，后续所有刷新的耗时都小于1秒。

解决办法 #

• 清除查询缓存再刷新
  在执行conn.Refresh前，添加代码重置查询的缓存状态，让Power Query重新拉取数据：

  ' 重置命令文本触发重新执行（针对OLEDB连接的Power Query）
  If conn.Type = xlConnectionTypeOLEDB Then
      Dim originalCmd As String
      originalCmd = conn.OLEDBConnection.CommandText
      conn.OLEDBConnection.CommandText = originalCmd
  End If
  

• 禁用Power Query的全局缓存设置
  打开Power Query编辑器，点击「文件」→「选项和设置」→「查询选项」，在「数据加载」里取消勾选「允许数据预览缓存」和「保留查询缓存」；也可以用VBA批量设置：

  Dim qry As WorkbookQuery
  For Each qry In ThisWorkbook.Queries
      If InStr(qry.Name, "test_") > 0 Then
          ' 强制查询每次刷新都重新获取数据
          qry.RefreshStyle = xlOverwriteCells
      End If
  Next qry
  

• 断开连接后再重新刷新
  刷新前先断开目标连接，确保每次都重新建立数据源连接：

  ' 先断开所有目标连接
  For Each conn In ThisWorkbook.Connections
      If InStr(1, conn.Name, "Query - test_") = 1 Then
          If conn.OLEDBConnection.IsConnected Then
              conn.OLEDBConnection.Close
          End If
      End If
  Next
  
  ' 再执行你的刷新逻辑（即原有代码部分）
  For Each conn In ThisWorkbook.Connections
      If InStr(1, conn.Name, "Query - test_") = 1 Then
          ' 原有刷新代码...
      End If
  Next
  

• 修改连接字符串绕过缓存
  每次刷新前给连接字符串加个随机参数，让Power Query认为是新的查询请求：

  Dim originalConnStr As String
  originalConnStr = conn.OLEDBConnection.Connection
  ' 添加随机参数，避免缓存
  conn.OLEDBConnection.Connection = originalConnStr & ";Random=" & Rnd() * 10000
  ' 刷新后恢复原连接字符串
  conn.Refresh
  conn.OLEDBConnection.Connection = originalConnStr
  

内容的提问来源于stack exchange，提问作者Ewan Brown