4、数据清洗与整理：使用Power Query进行列拆分、合并、去重、筛选、替换、数据类型转换等基础清洗操作
在经营分析看板搭建过程中，数据清洗往往占据整个项目40%以上的工作量。从ERP、CRM、Excel、OA等不同系统导出的原始数据，几乎不可避免地存在格式不统一、字段冗余、重复记录、空值、错误类型等问题。Power Query作为Power BI内置的数据整理引擎，提供了一套可视化、可复用的清洗流程。本章将围绕列拆分、合并、去重、筛选、替换、数据类型转换六大基础操作展开，帮助学员建立一套标准化的数据清洗方法论。
4.1 核心原理：为什么Power Query是数据清洗的首选工具
Power Query（在Power BI中称为“转换数据”）本质上是一个数据连接与整形引擎，它通过记录每一步操作生成M语言脚本，形成可重复执行的数据清洗流程。与传统Excel手工清洗相比，其核心优势在于：操作可追溯、流程可复用、刷新自动化。当源数据更新时，只需点击“刷新”即可自动完成全部清洗步骤，无需重复劳动。
在经营分析场景中，数据清洗的目标并非追求“干净”，而是达到“分析就绪”状态——即数据类型正确、关键字段无空值、重复记录已处理、维度字段格式统一。理解这一点，有助于避免过度清洗导致的数据失真或性能浪费。
4.2 六大基础操作详解
4.2.1 列拆分：从复合字段中提取结构化信息
操作场景：源数据中经常出现“部门-姓名-工号”“2024-01-15 14:30:22”这类复合字段，需要拆分为独立列以便分组、筛选和关联。
操作路径：选中目标列 → 右键或“转换”选项卡 → “拆分列” → 选择“按分隔符”“按字符数”或“按位置”。
| 拆分方式 | 适用场景 | 示例 | 注意事项 |
|---|---|---|---|
| 按分隔符 | 固定符号分隔的字段 | “华东-上海-浦东”按“-”拆为3列 | 可同时拆分到行，实现一维表转二维表 |
| 按字符数 | 定长编码字段 | “110105”按2位拆为“11”“01”“05” | 需确认源数据位数固定 |
| 按大写/小写转换 | 驼峰命名文本 | “salesAmount”拆为“sales”“Amount” | 较少用于经营分析场景 |
实战提示：拆分列时建议保留原始列作为备份，命名新列时使用清晰、业务化的名称（如“区域”“城市”），避免使用“Column1”“Column2”等默认命名。
4.2.2 列合并：构建业务所需的复合维度
操作场景：当分析需要“年份-月份”组合维度，或需要将“省”“市”合并为完整地址时，列合并功能可以快速实现。
操作路径：按住Ctrl选中多列 → “转换”选项卡 → “合并列” → 选择分隔符（建议使用“-”或“_”避免歧义）→ 输入新列名。
关键决策：合并列会改变数据粒度，需确认合并后的字段是否仍保持唯一性。例如，将“产品类别”和“产品名称”合并后，如果同一类别下存在重名产品，则合并字段无法作为唯一标识。
Table.CombineColumns 函数。如果合并后的列需要参与后续计算，建议在合并前先完成数据类型转换，避免文本与数字拼接导致类型错误。
4.2.3 去重：消除数据冗余，确保分析准确性
操作场景：从明细表汇总时，重复的订单号、重复的员工记录会导致计数虚高、金额翻倍等问题。
操作路径：选中需要判断重复的列 → 右键 → “删除重复项”。Power Query默认保留重复项中的第一行。
高级用法：当需要基于多列判断重复时（如“客户ID+订单日期”），需先选中所有参与判断的列再执行去重。若需保留特定行（如金额最大的记录），可先按该列排序，再去重。
| 去重场景 | 判断依据 | 保留策略 | M函数参考 |
|---|---|---|---|
| 订单明细去重 | 订单号 | 保留首行 | Table.Distinct |
| 客户去重 | 客户ID+手机号 | 保留最近消费记录 | Table.Group + Table.First |
| 产品维度去重 | 产品编码 | 保留最新价格 | Table.Sort + Table.Distinct |
4.2.4 筛选：聚焦分析范围，提升模型性能
操作场景：经营分析通常只关注特定期间、特定区域或特定产品线，提前筛选可减少数据加载量，提升报表响应速度。
操作路径：点击列标题右侧下拉箭头 → 选择“数字筛选器”“文本筛选器”或“日期筛选器” → 设置条件。
筛选类型对比：
- 行筛选：在Power Query中直接过滤行，减少进入模型的数据量。
- 列筛选：删除不需要的列，降低模型存储空间。
- 报表级筛选：在Power BI报表页使用切片器或筛选器，不改变底层数据。
注意事项：筛选操作应谨慎使用“保留行”功能，因为一旦筛选条件设置错误，可能导致关键数据被排除。建议在筛选前先创建数据预览，确认筛选结果符合预期。
4.2.5 替换：批量修正脏数据与异常值
操作场景：源数据中常见的“N/A”“#REF!”“未知”“-”等占位符，或“北京”“北京市”“北京 市”等不一致写法，需要通过替换统一。
操作路径：“转换”选项卡 → “替换值” → 输入“要查找的值”和“替换为”。
进阶技巧：
- 替换错误：使用“替换错误”功能将错误值统一替换为0或空值，避免后续计算报错。
- 条件替换：通过“条件列”功能实现更复杂的替换逻辑，如“当销售额<100时，标记为‘低’”。
- 批量替换：使用M函数
Text.Replace结合列表实现多值替换。
4.2.6 数据类型转换：为计算与分析奠定基础
操作场景：从文本文件导入的数据通常默认为文本类型，导致求和、平均值等聚合计算无法执行。日期字段若为文本格式，则无法进行时间智能分析。
操作路径：选中列 → “转换”选项卡 → “数据类型”下拉菜单 → 选择目标类型（整数、小数、日期、文本、布尔值等）。
| 源数据类型 | 目标类型 | 常见问题 | 解决方案 |
|---|---|---|---|
| 文本“1,234.56” | 小数 | 千位分隔符导致转换失败 | 先替换“,”为空，再转换 |
| 文本“2024/01/15” | 日期 | 格式不统一 | 使用“区域设置”指定日期格式 |
| 文本“是/否” | 布尔值 | 中文映射问题 | 先替换为TRUE/FALSE再转换 |
| 整数列含空值 | 整数 | 空值导致转换失败 | 先替换空值为0或删除行 |
最佳实践：数据类型转换应在清洗流程的后期执行，因为拆分、合并、替换等操作可能会改变数据类型。转换完成后，建议检查每列的数据类型图标，确保与业务语义一致。
4.3 案例拆解：销售订单表清洗实战
背景与目标：某零售企业需要搭建月度销售分析看板，源数据为从ERP系统导出的Excel文件，包含3万行订单记录。初始数据存在以下问题：订单日期为文本格式且包含时间戳、客户名称包含多余空格、产品编码与产品名称混在一列、存在重复订单记录、部分金额字段为文本且含千位分隔符。
清洗流程设计：
- 数据预览：通过“数据预览”功能快速识别各列质量（空值比例、唯一值数量、数据类型图标）。
- 列拆分：将“产品信息”列按“-”拆分为“产品编码”和“产品名称”两列。
- 列合并：将“年份”和“月份”两列合并为“年月”列，格式为“2024-01”。
- 去重：基于“订单号+产品编码”两列删除重复项，保留金额最大的记录（先按金额降序排序再去重）。
- 筛选：删除“订单状态”为“已取消”的行，仅保留“已完成”和“已发货”订单。
- 替换：将客户名称中的“（测试）”替换为空，将金额列中的“,”替换为空。
- 类型转换：将“订单日期”转换为日期格式（去除时间部分），将“销售额”“成本”转换为小数类型。
结果对比：
| 指标 | 清洗前 | 清洗后 | 变化说明 |
|---|---|---|---|
| 数据行数 | 30,000 | 27,842 | 去重+筛选减少2,158行 |
| 日期字段类型 | 文本 | 日期 | 支持时间智能分析 |
| 金额字段类型 | 文本 | 小数 | 支持聚合计算 |
| 产品维度 | 复合字段 | 编码+名称分离 | 支持按类别分析 |
复盘经验：清洗过程中发现约3%的订单存在重复记录，经排查是ERP系统接口重复推送导致。建议在数据源端增加唯一约束，从源头减少重复。同时，清洗步骤的命名应具有业务含义（如“删除取消订单”），便于后续维护和团队协作。
4.4 常见误区与总结复盘
误区一：过度清洗导致数据失真。例如，将空值一律替换为0，可能导致平均值计算偏低。正确做法是区分“真0值”和“缺失值”，根据业务含义决定填充策略。
误区二：忽略步骤顺序。先做类型转换再做拆分，可能导致拆分失败（如日期类型无法按“-”拆分）。建议遵循“先结构后类型”的原则：先拆分、合并、筛选，最后做类型转换。
误区三：不检查清洗结果。每次操作后应查看“数据预览”中的行数变化、错误提示和列质量指示器。建议在关键步骤后使用“保留行”或“删除行”功能验证数据量是否符合预期。
误区四：忽视M代码的可读性。虽然Power Query提供了可视化界面，但生成的M代码仍可通过“高级编辑器”查看和优化。建议在复杂清洗逻辑中添加注释（使用//），提高代码可维护性。