EP012 高级数据处理——修改特定单元格、逆透视与透视列

🔧 技巧一：如何修改特定单元格的值？（“迂回”战术）

在 Excel 表格中，想改哪个单元格直接双击即可。但在 Power Query (PQ) 中，我们无法直接编辑某个具体的单元格。
假如我们有一列（例如第2列），第一行是空值 null，我们需要把它手动命名为“费用科目”，该怎么办？

由于 PQ 是基于整列操作的，我们不能直接说“把第2行第2列改成xxx”。我们需要采用一种“迂回”的方法：

操作步骤：

1. 添加索引列 (Index Column)：
   • 点击 添加列 -> 索引列（从0或1开始均可）。
   • 目的：给每一行数据一个唯一的“身份证号”，以便我们定位特定行。
2. 创建条件列 (Conditional Column)：
   • 点击 添加列 -> 条件列。
   • 设置逻辑：
     • If（如果）索引列 等于 1（你想要修改的那一行的行号）；
     • Then（那么）输出值 “费用科目”；
     • Else（否则）输出选择原有的列（保持其他行数据不变）。
3. 清理辅助列：
   • 现在的“条件列”就是我们修正后的完美列。
   • 删除原始的列和辅助的索引列。

💡 思路总结在 PQ 中修改特定值，本质上是：定位（索引） -> 替换（逻辑判断） -> 覆盖。

---

📉 技巧二：逆透视列 (Unpivot) —— 宽表变长表

这是 Power Query 中最强大、最常用的功能之一。

场景描述

我们经常遇到一种“很宽”的报表（中国式报表）：

• 固定列：预算组织、费用科目。
• 动态列：后面跟着几十列，如“01期预算”、“01期调整”、“02期预算”……直到“12期”。
• 痛点：时间（期间）作为一个维度，被分散到了列标题中，导致表格极宽，且难以筛选特定期间的数据。

操作步骤

我们需要把这个“宽表”变成机器喜欢的“高表（长表）”。

1. 选中固定列：按住 Ctrl 键，选中“预算组织”和“费用科目”（即你不希望被打散的列）。
2. 执行逆透视：
   • 点击 转换 -> 逆透视列 下拉菜单。
   • 选择 【逆透视其他列 (Unpivot Other Columns)】。
3. 结果：
   • 所有原本横向排列的月份和指标，现在全部变成了一列“属性”和一列“值”。
   • 行数变化：数据量会暴增（例如从 3,500 行变成 450,000 行），这是正常的，因为数据结构变了。

后续清洗

逆透视后，“属性”列可能包含混合信息（如“01期_预算数”）。

• 拆分列：使用 按分隔符拆分（如按“_”），将“期间”和“指标名称”分离开来。

---

📈 技巧三：透视列 (Pivot) —— 长表变宽表

有时候逆透视做得“太彻底”了，把所有指标都变成了一列。
需求：我们希望“期间”（01期、02期）保持竖排（行维度），但希望具体的“指标”（预算数、调整数、执行数）变成横排（列维度），以便于计算（例如：列A/列B）。

这就需要用到透视列（逆透视的反向操作）。

操作步骤

1. 选中要透视的列：选中包含“预算数”、“调整数”这些名称的指标列。
2. 执行透视：
   • 点击 转换 -> 【透视列 (Pivot Column)】。
3. 设置参数（关键步骤）：
   • 值列 (Values Column)：选择存放具体金额的“金额列”。
   • 高级选项 (Advanced Options)：
     • 默认是“计数”或“求和”。
     • 如果你的数据已经是唯一的粒度（无需汇总），或者为了保持数据原貌，请选择 【不要聚合 (Don’t Aggregate)】。
4. 结果：
   • “预算数”、“调整数”等变成了独立的列。
   • “期间”依然保持在行维度。
   • 表格形态变得“不宽也不高”，是最适合进行数据分析和度量值计算的标准形态。

📊 数据形态的演变

1. 原始表：极宽（几千行，几十列） -> 难以分析
2. 逆透视后：极高（几十万行，3-4列） -> 适合机器存储，但指标混杂
3. 透视列后：标准（几万行，十几列） -> 适合建模计算（如：预算执行率 = 执行数 / 预算数）