Power Query完成后再刷新Power Pivot，解决透视表过滤器丢失问题

问题根源 #

核心问题是Excel默认的「全部刷新」会并行触发Power Query查询和数据模型（Power Pivot）的刷新流程。当Power Query尚未完成数据提取、转换并加载到模型时，数据模型已开始刷新，导致部分列的可选过滤值元数据未同步更新，最终引发透视表过滤器被清空。

解决方案：顺序刷新的VBA脚本 #

通过VBA强制按「Power Query刷新完成 → 数据模型刷新 → 透视表刷新」的顺序执行，彻底避免并行冲突。

代码实现 #

按Alt + F11打开VBA编辑器，插入新模块，粘贴以下代码：

Sub RefreshInOrder()
    ' 关闭屏幕更新和事件，提升效率
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    On Error GoTo Cleanup ' 错误处理，确保恢复设置
    
    ' 1. 刷新所有Power Query查询
    ThisWorkbook.Queries.Refresh
    
    ' 等待所有查询完成
    Do Until ThisWorkbook.Queries.FastCombine = False
        DoEvents ' 释放系统资源，防止假死
    Loop
    
    ' 2. 刷新Power Pivot数据模型
    ThisWorkbook.Model.Refresh
    
    ' 3. 刷新所有数据透视表
    Dim ws As Worksheet
    Dim pt As PivotTable
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws
    
Cleanup:
    ' 恢复Excel默认设置
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    If Err.Number <> 0 Then
        MsgBox "刷新出错：" & Err.Description, vbExclamation
    End If
End Sub

使用步骤 #

1. 将文件保存为.xlsm格式（必须启用宏）
2. 在Excel界面添加表单控件按钮（开发工具→插入→按钮），关联到RefreshInOrder宏
3. 告知员工替换源文件后，点击该自定义按钮完成刷新，替代默认的「全部刷新」

额外优化 #

• 若仅需刷新特定Power Query查询，可将ThisWorkbook.Queries.Refresh替换为ThisWorkbook.Queries("你的查询名称").Refresh
• 可在代码开头/结尾添加提示框，比如MsgBox "开始刷新，请等待..."和MsgBox "刷新完成"，提升用户体验
• 给员工做简短说明，避免误用默认刷新按钮

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