Power Query 数据清洗十大基础技巧
Power Query 是 Microsoft Excel 和 Power BI 中强大的数据清洗与转换工具，能够高效处理复杂数据集。本文详细介绍十大实用技巧，涵盖数据清洗的核心功能，并通过具体示例及对应的 M 代码帮助你快速上手。这些技巧将提升你的数据处理效率，确保数据准确、规范。
1. 去除空值和重复项
空值和重复数据会干扰分析结果，Power Query 提供简单工具来清理这些问题。使用“移除行”功能可以删除空值行，而“删除重复项”则能快速去除重复记录。
示例：
假设你有一个销售数据集，包含客户订单信息，但部分行由于录入错误为空，或者存在重复订单。
操作步骤：
- 打开 Power Query 编辑器，选择包含空值的列。
- 点击“主页” > “移除行” > “移除空行”。
- 对主键列（如订单 ID）右键选择“删除重复项”。
M 代码：
// 移除空行
Table.SelectRows(PreviousStep, each not List.IsEmpty(List.RemoveMatchingItems(Record.ToList(_), {null})))
// 删除重复项（基于“订单ID”列）
Table.Distinct(PreviousStep, {"订单ID"})结果：空行被删除，重复订单仅保留一条，数据更干净。
2. 拆分与合并列
当数据列包含多种信息（如姓名和地址混杂在一列）或需要整合多列信息时，拆分与合并功能非常实用。拆分可按分隔符（如逗号、空格）分割列，合并则将多列组合为一个。
示例：
数据集中的“客户信息”列包含“姓名,地址”格式，需要拆分为两列。
- 操作步骤：
- 选择“客户信息”列，点击“转换” > “拆分列” > “按分隔符”。
- 选择逗号作为分隔符，生成“姓名”和“地址”两列。
- 若需合并，选中多列，点击“转换” > “合并列”，指定分隔符（如“-”）。
M 代码：
// 拆分列
Table.SplitColumn(PreviousStep, "客户信息", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"姓名", "地址"})
// 合并列
Table.CombineColumns(PreviousStep, {"姓名", "地址"}, Combiner.CombineTextByDelimiter("-", QuoteStyle.None), "合并列")- 结果：数据结构更清晰，便于后续分析。
3. 数据类型规范化
正确的数据类型是分析的基础。Power Query 允许批量更改列的数据类型（如文本、数字、日期），避免因类型错误导致的计算问题。
示例：
日期列被识别为文本（如“2023-01-01”），导致无法进行时间计算。
- 操作步骤：
- 选择日期列，点击“转换” > “数据类型” > “日期”。
- 对金额列（如“$100”）使用“替换值”移除“$”，然后转换为“货币”或“数字”。
M 代码：
// 更改日期列类型
Table.TransformColumnTypes(PreviousStep, {{"日期列", type date}})
// 替换“$”并转换为数字
Table.ReplaceValue(PreviousStep, "#34;, "", Replacer.ReplaceText, {"金额列"}),
Table.TransformColumnTypes(PreviousStep, {{"金额列", Currency.Type}})- 结果：日期列可用于时间计算，金额列可用于数值运算。
4. 替换值
数据录入常有不一致问题，如大小写混杂、拼写错误或特殊值（如“NULL”）。Power Query 的“替换值”功能可批量修正。
示例：
产品名称列中，“Apple”和“apple”混杂，需统一为“Apple”。
- 操作步骤：
- 选择产品名称列，点击“转换” > “替换值”。
- 输入“apple”替换为“Apple”，可多次替换不同变体。
- 对“NULL”值替换为空白或“0”。
M 代码：
// 替换值
Table.ReplaceValue(PreviousStep, "apple", "Apple", Replacer.ReplaceText, {"产品名称"}),
Table.ReplaceValue(PreviousStep, "NULL", "", Replacer.ReplaceText, {"产品名称"})- 结果：数据一致性提升，避免因大小写差异导致的重复统计。
5. 过滤行
通过筛选功能，可以按条件保留所需数据，排除无关记录，缩小数据集范围。
示例：
你有一个销售数据集，只需分析2023年的订单。
- 操作步骤：
- 选择日期列，点击筛选下拉菜单。
- 选择“日期筛选” > “在...之后”和“在...之前”，设定2023年范围。
- 或对类别列筛选特定值（如“电子产品”）。
M 代码：
// 筛选2023年数据
Table.SelectRows(PreviousStep, each [日期列] >= #date(2023, 1, 1) and [日期列] <= #date(2023, 12, 31)),
// 筛选特定类别
Table.SelectRows(PreviousStep, each [类别] = "电子产品")- 结果：仅保留2023年数据，分析更聚焦。
6. 条件列创建
条件列基于逻辑规则（如 IF-THEN）生成新列，便于分类、标记或计算。
示例：
根据销售额标记订单为“高价值”或“低价值”。
- 操作步骤：
- 点击“添加列” > “条件列”。
- 设置规则：若“销售额”大于1000，则为“高价值”，否则为“低价值”。
M 代码：
// 添加条件列
Table.AddColumn(PreviousStep, "价值标签", each if [销售额] > 1000 then "高价值" else "低价值")- 结果：新列自动标记订单，方便后续分组分析。
7. 分组与聚合
分组功能可按指定列汇总数据，计算总和、平均值、计数等，适合生成汇总报表。
示例：
按产品类别统计总销售额和订单数。
- 操作步骤：
- 点击“主页” > “分组依据”。
- 选择“产品类别”作为分组列，添加聚合：
- “总销售额”：对“销售额”求和。
- “订单数”：计数所有行。
M 代码：
// 分组并聚合
Table.Group(PreviousStep, {"产品类别"}, {
{"总销售额", each List.Sum([销售额]), type number},
{"订单数", each Table.RowCount(_), Int64.Type}
})- 结果：生成汇总表，显示每类产品的总销售额和订单数。
8. 合并与追加查询
当数据分布在多个表中时，“合并查询”用于关联表，“追加查询”用于堆叠表。
示例：
你有“订单”表和“客户”表，需通过客户 ID 关联；另有多个地区的销售表需合并。
- 操作步骤：
- 合并：点击“主页” > “合并查询”，选择“订单”和“客户”表，指定客户 ID 关联。
- 追加：点击“主页” > “追加查询”，选择多个地区表，合并为单一表。
M 代码：
// 合并查询
Table.NestedJoin(PreviousStep, {"客户ID"}, 客户表, {"客户ID"}, "客户表", JoinKind.LeftOuter),
Table.ExpandTableColumn(PreviousStep, "客户表", {"客户姓名", "客户地址"}, {"客户姓名", "客户地址"}),
// 追加查询
Table.Combine({表1, 表2, 表3})- 结果：关联表提供完整信息，追加表整合所有地区数据。
9. 逆透视
逆透视（Unpivot）将宽表转为长表，适合将按列组织的属性数据转换为行，便于分析。
示例：
销售表按月度列显示销售额（宽表，如“2023-01”“2023-02”），需转为按月汇总（长表，包含“月份”和“销售额”列）。
- 操作步骤：
- 选择月份列（如“2023-01”“2023-02”），点击“转换” > “逆透视列”。
- 结果生成“属性”列（月份）和“值”列（销售额）。
M 代码：
// 逆透视
Table.UnpivotOtherColumns(PreviousStep, {"产品ID", "产品名称"}, "月份", "销售额")- 结果：宽表转为长表，方便时间序列分析。
10. 记录清洗步骤
Power Query 自动记录每一步操作，存储在“应用直步骤”面板中，确保清洗过程可追溯、可重复。
示例：
你对数据集执行了拆分列、替换值和筛选操作。
- 操作步骤：
- 在“应用步骤”面板查看每步记录。
- 点击步骤可回溯到对应状态，修改或删除操作。
- 保存查询为模板，复用于类似数据集。
M 代码：
// 示例完整查询
let
Source = Excel.CurrentWorkbook(){[Name="销售表"]}[Content],
Step1 = Table.SelectRows(Source, each not List.IsEmpty(List.RemoveMatchingItems(Record.ToList(_), {null}))),
Step2 = Table.ReplaceValue(Step1, "apple", "Apple", Replacer.ReplaceText, {"产品名称"}),
Step3 = Table.TransformColumnTypes(Step2, {{"日期列", type date}})
in
Step3- 结果：清洗过程透明，易于调试和复用。
总结
Power Query 的强大之处在于其直观界面和自动化记录功能。以上十大技巧涵盖了从基础清理到高级转换的核心功能，通过示例和 M 代码展示如何实际应用。建议保存常用查询模板，并定期检查“应用步骤”以优化流程。这些技巧将帮助你高效清洗数据，为分析和可视化奠定坚实基础。
