使用Power Query替换同名同列Excel文件实现数据自动更新

问题背景 #

针对8个城市的调研项目制作多类图表，每个城市调研数据行数不固定。希望通过Power Query实现替换同名文件即可切换不同城市数据，且Excel工作表导入数据自动更新，但替换文件后出现报错。

原使用代码 #

let
    Source = Excel.Workbook(File.Contents("C:\Users\user\OneDrive\Documents\results-city.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}})
in
    #"Changed Type"

报错原因 #

1. 硬编码Sheet名称：指定固定Sheet1，若替换的文件工作表名称不同则直接报错
2. 固定列类型转换：硬编码指定Column1/2/3为文本类型，新文件列数、列名或数据类型不匹配时触发错误
3. 文件路径与同步问题：OneDrive路径可能存在文件锁定、同步延迟，导致Power Query无法正常读取替换后的文件

解决方案 #

优化后的Power Query代码 #

以下代码适配不同工作表名称、自动识别列类型，避免硬编码带来的兼容性问题：

let
    Source = Excel.Workbook(File.Contents("C:\Users\user\OneDrive\Documents\results-city.xlsx"), null, true),
    // 获取文件夹中第一个工作表，无需指定固定名称
    FirstWorksheet = Source{0}[Data],
    // 自动将第一行设为表头（需确保所有替换文件的表头结构一致）
    #"Promoted Headers" = Table.PromoteHeaders(FirstWorksheet, [PromoteAllScalars=true]),
    // 自动检测并转换列类型，适配不同数据类型
    #"Auto-Detected Column Types" = Table.AutoDetectColumnTypes(#"Promoted Headers", null)
in
    #"Auto-Detected Column Types"

额外注意事项 #

• 替换文件时，确保新文件的表头结构与原文件完全一致，否则图表会因列名引用失效报错
• 替换文件前务必关闭原文件，避免文件锁定导致Power Query读取失败
• 在Excel「数据」选项卡中开启「刷新时忽略隐私级别设置」，并可设置定时自动刷新（右键查询→属性→勾选打开文件时刷新）

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