如何在Power Query中给指定行以下的含值单元格添加STR前缀？

需求 #

要把这份横向的新旧商品替换清单，给StoreCount行下方的所有非空单元格加上STR前缀（区分门店号与商品号），再转成每个门店对应一组商品替换信息的纵向表格，最终效果如下：

原始数据 #

|          | Swap #1| Swap #2|
|Group     | Food   | Drinks |
|Drop item | 111    |  222   |
|New Item  | 112    |  223   |
|StoreCount|  2     |   1    |
|          |  114   |   10   |
|          |  5     |        |

操作步骤 & 完整M代码 #

复制以下代码到Power Query高级编辑器，把Table1替换成你实际的表名即可：

let
    // 读取Excel中的原始表格
    源 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    // 添加索引列，用于定位StoreCount所在行
    带索引 = Table.AddIndexColumn(源, "行索引", 0, 1, Int64.Type),
    // 获取StoreCount行的索引位置
    StoreCount行位置 = List.PositionOf(带索引[Column1], "StoreCount"),
    // 给StoreCount行下方的非空单元格批量添加STR前缀
    添加前缀后 = Table.TransformColumns(带索引, 
        List.Transform(List.Skip(Table.ColumnNames(带索引),1), (列名) => {
            列名, (单元格值) => if [行索引] > StoreCount行位置 and 单元格值 <> null then "STR" & Text.From(单元格值) else 单元格值
        })
    ),
    // 删除索引列并转置表格
    移除索引 = Table.RemoveColumns(添加前缀后,{"行索引"}),
    转置表 = Table.Transpose(移除索引),
    // 将第一行设为表头
    设置表头 = Table.PromoteHeaders(转置表, [PromoteAllScalars=true]),
    // 逆透视Swap列，把横向商品组转为纵向结构
    逆透视 = Table.UnpivotOtherColumns(设置表头, {"Group", "Drop item", "New Item", "StoreCount"}, "Swap编号", "值"),
    // 从带STR标记的值中提取门店编号
    提取门店 = Table.AddColumn(逆透视, "Store", each if Text.StartsWith([值], "STR") then Text.AfterDelimiter([值], "STR") else null),
    // 填充商品信息（用FillDown补全空值）
    填充商品信息 = Table.FillDown(Table.SelectRows(提取门店, each [Store] = null), {"Group", "Drop item", "New Item"}),
    // 通过Swap编号关联门店信息与对应商品组
    关联门店与商品 = Table.NestedJoin(填充商品信息, {"Swap编号"}, Table.SelectRows(提取门店, each [Store] <> null), {"Swap编号"}, "门店信息", JoinKind.LeftOuter),
    // 展开门店信息列
    展开门店 = Table.ExpandTableColumn(关联门店与商品, "门店信息", {"Store"}, {"Store"}),
    // 整理列名与结构，删除错误行
    整理最终表 = Table.RenameColumns(
        Table.SelectColumns(Table.RemoveRowsWithErrors(展开门店), {"Store", "Drop item", "New Item", "Group"}),
        {{"Drop item", "Drop Item"}}
    )
in
    整理最终表

关键逻辑说明 #

1. 前缀添加：通过索引列精准定位StoreCount行，对该行之后的非空单元格批量加前缀，快速区分门店号与商品编号。
2. 结构转换：用转置+逆透视把横向的商品组转为纵向，再通过Swap编号绑定门店与对应的商品替换信息。
3. 信息补全：用FillDown补全商品信息空值，确保每个门店都能匹配到对应的新旧商品与分组。

内容的提问来源于stack exchange，提问作者Dan Wilson