Power Query：导入、清洗、合并与刷新外部数据

适用场景

每个月都要把十几个 CSV 合到一起、把宽表转成长表、把两张表按编号关联合并、把重复的清洗步骤重复做一遍。Power Query 把这些步骤记录成可刷新的查询：源数据更新后点“刷新全部”，结果自动重算，不用重新手工操作。

Excel 2016 及以上内置在“数据 → 获取和转换数据”；Excel 2010/2013 需要安装 Power Query 加载项。Microsoft 365 的入口叫“数据 → 获取数据”。Windows 版功能最完整，Mac 版部分连接器和功能受限。

基本流程

1. “数据 → 获取数据”，选择来源：
   • 从文件：工作簿、CSV、文本、文件夹；
   • 从表格/区域：当前工作表里的数据；
   • 从数据库、Web、SharePoint 等。
2. 在导航器里勾选要导入的表，点“转换数据”进入 Power Query 编辑器。
3. 在编辑器里清洗：
   • 删除不需要的列；
   • 筛选掉空行、合计行；
   • 修改数据类型；
   • 拆分列、替换值、去重；
   • 逆透视、合并、追加查询。
4. “关闭并上载”到工作表或数据模型；也可以只建立连接，供透视表使用。

每一步都会记录在右侧“应用的步骤”里，可以点回某一步查看中间结果，也可以删除或重排步骤。

常用转换

合并查询

“合并查询”类似 SQL JOIN：

• 选择两张表和各自的匹配列；
• 连接种类：左外部、右外部、完全外部、内部、左反、右反；
• 合并后得到一列 Table，点右上角展开按钮选择要保留的列；
• 展开时注意取消“使用原始列名作为前缀”，否则列名会变成“表1.字段”。

逆透视

宽表（月份在列上）转长表（一行一个月份）：

1. 选中要保留的标识列（如产品、地区）；
2. “转换 → 逆透视其他列”；
3. 把生成的“属性”列改名为“月份”，“值”列改名为“销售额”；
4. 修改数据类型。

逆透视后的长表适合做透视表、图表和后续合并。

从文件夹合并

1. “数据 → 获取数据 → 从文件 → 从文件夹”；
2. 选择文件夹；
3. 在导航器里点“转换数据”；
4. 保留 Content 列，删除其他辅助列；
5. 点 Content 列标题右侧的“合并文件”按钮；
6. 确认示例文件结构，Power Query 会自动生成一个合并查询。

文件新增或改名后，刷新即可重新扫描文件夹。文件名、工作表名、表头变化会导致步骤报错，需要回到对应步骤修正。

加载方式

加载到工作表受 1,048,576 行限制；更大的数据建议加载到数据模型。加载到数据模型后，透视表可以勾选“将此数据添加到数据模型”来做非重复计数。

刷新

• “数据 → 刷新全部”刷新所有查询；
• 右键查询 →“属性”可以设置打开文件时刷新、定期刷新；
• 源文件路径、工作表名、表头列名变化会导致刷新失败；
• 刷新会覆盖加载到工作表的结果区域，不要在结果区域里手工加列。

易错点

• 源文件被移动或改名：查询里保存的是绝对路径；移动后要在“数据源设置”里改路径。
• 工作表名或表名变了：步骤引用旧名称会报错；尽量让源工作表名稳定。
• 表头不在第一行：用“删除顶部行”或“将第一行用作标题”调整；表头重复或为空会导致列名自动编号。
• 数据类型自动猜错：日期被识别成文本、数字带前导零被转成数字；在“更改类型”里手动指定，前导零列用文本。
• 合并后行数变多：一对多关联合会放大行数；合并前先确认匹配列是否唯一。
• 展开列时带前缀：勾选“使用原始列名作为前缀”会让后续步骤引用变复杂；不需要时取消。
• 隐私级别阻止合并：不同来源的隐私级别可能阻止合并；在“数据源设置 → 隐私级别”里按组织策略调整，不要随意设为“无”。
• 刷新很慢：尽早删除不需要的列、筛选掉无关行；数据库源尽量让查询折叠下推，文件源则减少重复扫描。
• 结果区域被手工修改：刷新会覆盖；需要额外计算时在结果表旁边加列，不要改结果本身。

相关条目

• excel-pivot-table — 用查询结果做透视表
• excel-dynamic-arrays — 轻量筛选与合并
• excel-sort-filter — 小数据量的手工清洗
• excel-basics — 表格与数据类型
• excel-macro-recording — 用宏自动化重复操作

参考链接

• Microsoft Learn: Power Query
• Microsoft Support: Excel

读完自测

合上页面，先回答，再展开对照。

1. Power Query 的“应用的步骤”有什么用？
2. 宽表转长表应该用哪个转换？
3. 刷新后为什么手工加在结果表里的列消失了？