如何用Power Query拆分多列并保留指定单元格合并状态？

原始数据 #

需求 #

拆分Reference 1（空格分隔）和Delivery（姓名前缀/换行分隔）为多行，使每个参考编号对应一条配送信息，同时保留Job No.、Date、Miles、Price的原始对应关系，避免冗余。目标输出如下：

解决方案（Power Query） #

可视化操作流程 #

1. 导入数据：Excel中点击数据→获取数据→从文件→从CSV，导入目标文件进入Power Query编辑器。
2. 拆分Reference列为行：选中Reference 1列，转换→拆分列→按分隔符，选择空格，勾选拆分为行，确定。
3. 修复Delivery列分隔：选中Delivery列，转换→替换值，将Mr替换为#lfMr、Mrs替换为#lfMrs；接着拆分列→按分隔符，输入#lf，勾选拆分为行，确定。
4. 过滤空值：开始→行→删除空行，清理拆分后产生的空Delivery条目。
5. 分组匹配对应行：开始→分组依据，分组列选Job No.和Date，新列名设为匹配数据集，操作选所有行，确定。
6. 展开关联数据：点击匹配数据集列的展开按钮，选择添加自定义列，输入以下代码：

   = Table.FromRecords(
       List.Zip({
           [Reference 1],
           [Delivery],
           List.Repeat({[Miles]{0}}, List.Count([Reference 1])),
           List.Repeat({[Price]{0}}, List.Count([Reference 1]))
       })
       ,{"Reference 1", "Delivery", "Miles", "Price"}
     )
   

   展开该自定义列，保留所有需要的字段即可完成整理。

完整M代码（直接替换使用） #

let
    Source = Csv.Document(File.Contents("替换为你的CSV文件路径"),[Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"提升标题" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"更改数据类型" = Table.TransformColumnTypes(#"提升标题",{{"Job No.", type text}, {"Date", type datetime}, {"Reference 1", type text}, {"Delivery", type text}, {"Miles", Int64.Type}, {"Price", type number}}),
    #"替换Mr分隔符" = Table.ReplaceValue(#"更改数据类型","Mr","#lfMr",Replacer.ReplaceText,{"Delivery"}),
    #"替换Mrs分隔符" = Table.ReplaceValue(#"替换Mr分隔符","Mrs","#lfMrs",Replacer.ReplaceText,{"Delivery"}),
    #"拆分Reference为行" = Table.ExpandListColumn(Table.TransformColumns(#"替换Mrs分隔符", {{"Reference 1", Splitter.SplitTextByWhitespace, let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Reference 1"),
    #"拆分Delivery为行" = Table.ExpandListColumn(Table.TransformColumns(#"拆分Reference为行", {{"Delivery", Splitter.SplitTextByDelimiter("#lf", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Delivery"),
    #"过滤空Delivery行" = Table.SelectRows(#"拆分Delivery为行", each [Delivery] <> ""),
    #"分组匹配数据" = Table.Group(#"过滤空Delivery行", {"Job No.", "Date"}, {{"匹配行", each Table.FromRecords(List.Zip({[Reference 1],[Delivery],List.Repeat({[Miles]{0}},List.Count([Reference 1])),List.Repeat({[Price]{0}},List.Count([Reference 1]))}),{"Reference 1","Delivery","Miles","Price"})}}),
    #"展开匹配结果" = Table.ExpandTableColumn(#"分组匹配数据", "匹配行", {"Reference 1", "Delivery", "Miles", "Price"}, {"Reference 1", "Delivery", "Miles", "Price"})
in
    #"展开匹配结果"

内容的提问来源于stack exchange，提问作者Zoltán Farkas