如何通过Power Query同步Excel模板列结构且保留业务数据？

核心思路 #

默认Power Query连接会直接用Template的空表替换现有数据，要解决问题，必须修改查询逻辑：将SalesPerson.xlsx中已填写的数据，与Template的最新列结构做匹配合并——保留匹配列的已有数据，新增列留空，同时支持列名变更的映射。

具体实现步骤 #

步骤1：备份已填写的数据 #

在SalesPerson.xlsx中新建一张工作表（比如命名为「原始数据」），把当前Power Query生成表中的所有已填数据复制到这张表，按Ctrl+T将其转换为Excel表，命名为SalesRawData。

步骤2：修改Power Query查询逻辑 #

打开Power Query编辑器（点击「数据」选项卡 → 现有连接 → 找到连接Template的查询 → 编辑），替换原有逻辑为以下流程：

1. 加载Template的最新列结构
   保留原有加载Template.xlsx表的步骤，确保只获取表头结构（因为Template是空表），将这个步骤命名为TemplateSchema。
2. 引入已备份的原始数据
   添加新步骤，加载SalesRawData表：

   SalesRawData = Excel.CurrentWorkbook(){[Name="SalesRawData"]}[Content]
   

3. 处理列名映射（应对列名修改场景）
   如果需要支持列名变更（比如Template的OrderNo.改成OrderID），在SalesPerson.xlsx中新建一张映射表ColumnNameMap（包含「原列名」「新列名」两列），然后在Power Query中读取并应用映射：

   ColumnMap = Excel.CurrentWorkbook(){[Name="ColumnNameMap"]}[Content]
   SalesDataRenamed = Table.RenameColumns(SalesRawData, Table.ToList(ColumnMap, each {_[原列名], _[新列名]}))
   

   如果不需要处理列名修改，可跳过此步骤，直接用SalesRawData进行后续操作
4. 匹配并合并列结构
   添加步骤，提取Template的列名，匹配已有数据的列，再补充新增列：

   // 获取Template的所有列名
   TemplateColumns = Table.ColumnNames(TemplateSchema)
   // 获取已处理后的数据列名
   ExistingColumns = Table.ColumnNames(SalesDataRenamed)
   // 保留两者共有的列
   MatchedData = Table.SelectColumns(SalesDataRenamed, List.Intersect({TemplateColumns, ExistingColumns}))
   // 添加Template中新增的列，值为空
   FinalTable = Table.AddColumns(MatchedData, List.Difference(TemplateColumns, ExistingColumns), each null)
   

5. 设置加载选项
   将FinalTable加载回Excel，选择「仅创建连接」，再将连接加载到现有工作表（替换原来的Power Query表），勾选「启用加载」和「添加到数据模型」。

步骤3：后续刷新操作 #

• 当Template.xlsx新增列或修改列名后，先更新ColumnNameMap（如果有列名变更），然后在SalesPerson.xlsx中点击「数据」→「全部刷新」。
• 刷新后，已有数据会保留在匹配的列中，新增列自动添加且为空，列名变更的列会同步更新并保留原有数据。

注意事项 #

• 「原始数据」表不要手动修改，所有业务数据仍在Power Query生成的表中填写，「原始数据」仅作为备份供Power Query读取。
• 如果Template删除了某列，刷新后SalesPerson.xlsx中对应的列会被移除，建议提前备份相关数据。

内容的提问来源于stack exchange，提问作者Łukasz Obiedziński