Power Query 数据清洗十大基础技巧

B站影视 内地电影 2025-08-05 07:59 1

摘要: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”),导致无法进行时间计算。

操作步骤:选择日期列,点击“转换” > “数据类型” > “日期”。对金额列(如“$100”)使用“替换值”移除“$”,然后转换为“货币”或“数字”。

M 代码

// 更改日期列类型Table.TransformColumnTypes(PreviousStep, {{"日期列", type date}})// 替换“$”并转换为数字Table.ReplaceValue(PreviousStep, "$", "", Replacer.ReplaceText, {"金额列"}),Table.TransformColumnTypes(PreviousStep, {{"金额列", Currency.Type}})结果:日期列可用于时间计算,金额列可用于数值运算。

数据录入常有不一致问题,如大小写混杂、拼写错误或特殊值(如“NULL”)。Power Query 的“替换值”功能可批量修正。

示例
产品名称列中,“Apple”和“apple”混杂,需统一为“Apple”。

操作步骤:选择产品名称列,点击“转换” > “替换值”。输入“apple”替换为“Apple”,可多次替换不同变体。对“NULL”值替换为空白或“0”。

M 代码

// 替换值Table.ReplaceValue(PreviousStep, "apple", "Apple", Replacer.ReplaceText, {"产品名称"}),Table.ReplaceValue(PreviousStep, "NULL", "", Replacer.ReplaceText, {"产品名称"})结果:数据一致性提升,避免因大小写差异导致的重复统计。

通过筛选功能,可以按条件保留所需数据,排除无关记录,缩小数据集范围。

示例
你有一个销售数据集,只需分析2023年的订单。

操作步骤:选择日期列,点击筛选下拉菜单。选择“日期筛选” > “在...之后”和“在...之前”,设定2023年范围。或对类别列筛选特定值(如“电子产品”)。

M 代码

// 筛选2023年数据Table.SelectRows(PreviousStep, each [日期列] >= #date(2023, 1, 1) and [日期列] 结果:仅保留2023年数据,分析更聚焦。

条件列基于逻辑规则(如 IF-THEN)生成新列,便于分类、标记或计算。

示例
根据销售额标记订单为“高价值”或“低价值”。

操作步骤:点击“添加列” > “条件列”。设置规则:若“销售额”大于1000,则为“高价值”,否则为“低价值”。

M 代码

// 添加条件列Table.AddColumn(PreviousStep, "价值标签", each if [销售额] > 1000 then "高价值" else "低价值")结果:新列自动标记订单,方便后续分组分析。

分组功能可按指定列汇总数据,计算总和、平均值、计数等,适合生成汇总报表。

示例
按产品类别统计总销售额和订单数。

操作步骤:点击“主页” > “分组依据”。选择“产品类别”作为分组列,添加聚合:“总销售额”:对“销售额”求和。“订单数”:计数所有行。

M 代码

// 分组并聚合Table.Group(PreviousStep, {"产品类别"}, {{"总销售额", each List.Sum([销售额]), type number},{"订单数", each Table.RowCount(_), Int64.Type}})结果:生成汇总表,显示每类产品的总销售额和订单数。

当数据分布在多个表中时,“合并查询”用于关联表,“追加查询”用于堆叠表。

示例
你有“订单”表和“客户”表,需通过客户 ID 关联;另有多个地区的销售表需合并。

操作步骤:合并:点击“主页” > “合并查询”,选择“订单”和“客户”表,指定客户 ID 关联。追加:点击“主页” > “追加查询”,选择多个地区表,合并为单一表。

M 代码

// 合并查询Table.NestedJoin(PreviousStep, {"客户ID"}, 客户表, {"客户ID"}, "客户表", JoinKind.LeftOuter),Table.ExpandTableColumn(PreviousStep, "客户表", {"客户姓名", "客户地址"}, {"客户姓名", "客户地址"}),// 追加查询Table.Combine({表1, 表2, 表3})结果:关联表提供完整信息,追加表整合所有地区数据。

逆透视(Unpivot)将宽表转为长表,适合将按列组织的属性数据转换为行,便于分析。

示例
销售表按月度列显示销售额(宽表,如“2023-01”“2023-02”),需转为按月汇总(长表,包含“月份”和“销售额”列)。

操作步骤:选择月份列(如“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}})inStep3

Power Query 的强大之处在于其直观界面和自动化记录功能。以上十大技巧涵盖了从基础清理到高级转换的核心功能,通过示例和 M 代码展示如何实际应用。建议保存常用查询模板,并定期检查“应用步骤”以优化流程。这些技巧将帮助你高效清洗数据,为分析和可视化奠定坚实基础。

来源:数据分析精选

相关推荐