Power Query 教程
什么是 Power Query 以及它为什么改变一切
Power Query 是 Excel 内置的 ETL（提取、转换、加载）引擎。用通俗的话说：它是一个从几乎任何来源导入数据、自动清洗和重塑数据、并将其加载到工作表中的工具——并且每一步都被记录并可重复执行。如果你曾花整个周五下午手动清洗每周以同样混乱格式到达的报表，Power Query 就是你一直等待的解决方案。
与宏不同，Power Query 不需要编码。你通过可视化界面构建转换步骤，Power Query 在后台用 M 语言记录它们。当下周的文件到达时，点击一次即可重新应用所有清洗步骤。
分步操作：自动化周销售报表清洗
场景：每周一你会收到 Sales_YYYYMMDD.csv 文件，日期格式不一致、产品类别是合并的（类别-子类别在一列中）、部分行缺少销售金额、底部有多余汇总行。构建一个 Power Query 自动清洗。
第一步——导入原始数据
- 数据 > 获取数据 > 从文件 > 从文本/CSV。
- 选择你的销售 CSV 文件。导航器预览数据。注意 Power Query 已经检测到分隔符和数据类型。
- 点击转换数据（而非"加载"）。打开 Power Query 编辑器——所有清洗在此进行。
第二步——提升标题并删除垃圾行
- 如果第一行是标题：开始 > 将第一行用作标题。始终先做这一步——标题启用了后续步骤中的列名引用。
- 删除底部的汇总行。筛选日期列：点击下拉列表，取消勾选包含"合计"等文字的行或空值。或者如果你知道底部有几个多余行，使用开始 > 删除行 > 删除最后几行。
- 删除完全空白的行：开始 > 删除行 > 删除空行。
第三步——拆分产品类别列
- 你的产品列包含"电子产品-配件"——类别连字符子类别。你需要两列。
- 选中产品列。转换 > 拆分列 > 按分隔符。
- 分隔符：自定义，输入
-。拆分位置：最左侧的分隔符（重要：某些子类别包含连字符，如"视听设备"）。 - 点击确定。现在你有产品.1（类别）和产品.2（子类别）。重命名：右键列标题 > 重命名。
第四步——修正日期格式并处理缺失值
- 选中日期列。转换 > 数据类型 > 日期。如果某些日期无法转换（显示错误），点击列下拉 > 替换错误 > 输入今天日期作为后备，或筛选这些行进行审查。
- 选中销售金额列。转换 > 替换值。要查找的值：
null，替换为：0。将缺失销售替换为零，而不是留空。 - （可选）删除销售金额为 0 的行：筛选销售金额 > 数字筛选器 > 大于 > 0。
第五步——加载并设置自动刷新
- 开始 > 关闭并上载至。选择"表"和"新工作表"。点击确定。
- 清洗后的数据出现在 Excel 中。现在自动化：数据 > 查询和连接（右侧窗格）。
- 右键你的查询 > 属性。勾选打开文件时刷新数据。可选设置每 X 分钟刷新一次用于实时仪表盘。
- 下周：将新 CSV 以相同名称保存在相同位置，打开此工作簿，点击数据 > 全部刷新。所有清洗步骤自动重放。
关键技巧
- 逆透视以获得分析友好的数据。如果你的数据有月份作为单独的列（1月、2月、3月），选中描述性列，转换 > 逆透视其他列。宽表变成数据透视表友好的高表。
- 合并查询代替 VLOOKUP。开始 > 合并查询 基于匹配列连接两个表——Power Query 版的 VLOOKUP，但能处理数百万行和多种连接类型（左、右、完全外部、内部、反）。
- 分组依据用于汇总。转换 > 分组依据 按类别汇总数据（求和、计数、平均值）——就像数据到达工作表之前运行的数据透视表。
- 重命名步骤以保持清晰。"更改的类型"、"删除的列"和"筛选的行"在有 20 个步骤时变得毫无意义。右键步骤 > 重命名 描述它做什么："删除空行"或"拆分全名"。
常见错误
- 不必要地加载数百万行。加载前先筛选行。开发期间使用开始 > 保留行 > 保留前几行，准备好使用完整数据后再移除筛选器。
- 不显式修正数据类型。Power Query 会猜测数据类型但可能猜错。选中每列并使用开始 > 数据类型正确设置：ID 用文本，货币用小数，日期用日期。不正确的数据类型是 Power Query 最常见错误的来源。
- 忘记 Power Query 区分大小写。与 Excel 公式不同，M 语言和文本筛选器区分大小写。"ABC"在筛选器或合并中不匹配"abc"，除非先应用大写或小写转换。
- 在单个步骤中过度嵌套转换。每个逻辑转换使用独立的步骤。独立的步骤更容易调试、重新排序和向同事解释。
进阶技巧
- 自动合并文件夹中的文件。获取数据 > 从文件 > 从文件夹，然后点击合并 > 合并并转换。Power Query 将你的转换应用到文件夹中的每个文件。放入新文件并刷新——这样实现了周报自动合并。
- 参数实现动态查询。开始 > 管理参数 让你创建命名值（文件路径、日期范围、阈值），用户无需编辑查询即可更改。在筛选步骤中引用参数，实现自助报表。
- 使用 Try Otherwise 进行错误处理。用
try ... otherwise ...包裹转换：try Date.FromText([列]) otherwise null。防止整个查询因为一个坏单元格而失败。