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