Excel Power Pivot如何仅保存筛选后数据模型并移除查询与连接

推荐方案：VBA批量自动化处理（适配每周多供应商批量生成场景） #

• 该方案全程在Excel进程内完成，不需要借助外部工具，生成的文件无任何原数据库连接、无全量数据残留，单文件支持千万行级模型数据，一次配置后每周刷新数据仅需一键运行即可生成所有供应商专属报表。
• 操作步骤：
  1. 固定原工作簿内所有需要下发的透视表布局、度量值，记录供应商维度的完整字段名（例如Dim_Supplier[SupplierName]），提前整理好所有需要下发的供应商名称/ID清单，存放在单独的隐藏工作表中供宏调用。
  2. 按Alt+F11打开VBA编辑器，插入标准模块，粘贴如下核心处理代码，根据自己的实际表名、字段名、保存路径调整参数即可：

Sub 批量生成供应商报表()
    Dim wbSource As Workbook, wbTarget As Workbook
    Dim supList As Range, supCell As Range
    Dim modelConn As WorkbookConnection
    Dim savePath As String
    
    Set wbSource = ThisWorkbook
    savePath = wbSource.Path & "\供应商报表\"
    If Dir(savePath, vbDirectory) = "" Then MkDir savePath
    Set supList = wbSource.Sheets("供应商清单").Range("A2:A" & wbSource.Sheets("供应商清单").Cells(Rows.Count, "A").End(xlUp).Row)
    
    For Each supCell In supList
        ' 给原模型施加全局供应商筛选
        wbSource.Model.ModelMeasures("全局供应商筛选").Formula = "=IF(VALUES(Dim_Supplier[SupplierName])=""" & supCell.Value & """,1,0)"
        wbSource.Model.ApplyFilterToModel
        ' 复制指定报表页为新工作簿
        wbSource.Sheets(Array("采购数据报表", "绩效统计报表")).Copy
        Set wbTarget = ActiveWorkbook
        ' 删除新工作簿所有外部连接
        For Each modelConn In wbTarget.Connections
            modelConn.Delete
        Next
        ' 清除所有查询残留
        On Error Resume Next
        wbTarget.Queries.Delete
        On Error GoTo 0
        ' 按供应商命名保存
        wbTarget.SaveAs Filename:=savePath & supCell.Value & "周度报表.xlsx", FileFormat:=xlOpenXMLWorkbook
        wbTarget.Close SaveChanges:=False
    Next supCell
    MsgBox "所有供应商报表生成完成"
End Sub

3. 首次运行前在Power Pivot模型里新建一个名为全局供应商筛选的度量值，关联到供应商维度表即可，后续每周原数据刷新完成后，直接运行宏就能自动完成所有供应商的筛选、断连、保存操作。

• 合规性说明：新工作簿生成时，模型里只会写入当前筛选上下文可见的单供应商数据，原全量数据、连接字符串、Power BI查询链路都会被彻底清除，透视表会自动绑定到本地留存的筛选后模型，不会失效。

备选方案：手动模型筛选导出（适配供应商数量少于5家的场景） #

如果环境不允许启用宏，可以直接通过Power Pivot内置功能完成单供应商文件导出，不需要转普通表、不需要导CSV：

• 打开原工作簿进入Power Pivot管理界面，针对所有存储明细数据的事实表，通过DAX公式添加行级筛选，例如对订单事实表应用筛选规则=FILTER('Fact_Purchase', 'Fact_Purchase'[SupplierName] = "目标供应商名称")，逐表过滤掉不属于当前供应商的所有行。
• 回到Excel主界面，进入「数据」选项卡打开「连接」面板，选中所有指向Power BI服务的外部连接，执行删除操作，系统提示透视表将使用本地模型缓存时直接确认即可。
• 打开「查询和连接」面板，删除所有残留的Power Query查询，检查文件属性清除所有连接字符串残留后，直接另存为对应供应商的报表文件即可。
• 该方案同样支持百万行级模型数据，因为数据全程存储在Power Pivot的xVelocity压缩引擎中，不占用普通工作表的104万行上限，单文件处理时长通常在2分钟以内。

常见误区纠正 #

• DAX Studio导出3万行是旧版本默认的预览行限制，调整导出配置后支持千万行级数据导出，但需要额外走CSV导出、再导入新模型的流程，效率远低于上述两种Excel内直接处理的方案。
• 删除连接后透视表失效的问题，本质是没有先在模型层完成数据留存，只要模型内已经保留了筛选后的本地数据，删除外部连接不会导致透视表失效。
• 普通工作表百万行超限不影响Power Pivot模型存储，只要生成的文件大小不超过Excel工作簿2GB的上限即可正常使用，百万行级的业务数据经VertiPaq引擎压缩后通常仅为几十到数百MB，完全在支持范围内。

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