如何利用Power Query将订单号与订单内容进行匹配并拆分多行

在实际的工作中，会遇到如下的情况，系统格式整合把同一时间生成的订单整合成了一条记录，这种情况不好对后续的统计进行处理，如实际出单数、出货数量等等。

当然最好的方式是在中台系统设计的时候就考虑到后续统计人员的难处，让导出的Excel表格更加适合统计和汇报。（下图是要求处理成的格式）

先说思路：

在Excel中，对于订单号这种有明显分隔符的可以用“分列”功能生成新列，而Power Query则提供了更高级的分列功能，可以将其按行的形式进行分列，通过对订单号进行分列，可以得出以下结果

通过观察可以知道，Power Query的拆分列中的“行”功能可以拆分的同时完成对其他单元格信息的复制，但【购买内容】单元格也进行复制了。因此可以想到：寻找办法能够将把需要进行拆分的信息缝合到一列里面，然后再进行拆分，然后再用普通的分列功能拆分成多列

而列的融合，就想到可以用以下的函数了：

List.Zip

使用示例：

（1）两个列表

List.Zip({{"孙兴华", "李小龙"}, {20, 80}})      返回：{{"孙兴华", 20},{"李小龙",80}}

（2）三个列表

List.Zip({{"孙兴华", "李小龙"}, {20, 80},{"健身","截拳道","咏春"}})     

返回：{{"孙兴华", 20,"健身"},{"李小龙",80,"截拳道"},{null,null,"咏春"}}

在这之前，先对【订单号】和【购买内容】两列进行拆分，变成可融合的列表

用Table.TransformColumns对表格中的具体列进行调用操作，用Text.SplitAny把单元格按照字符拆分列表，其中#(lf)为回车符

随后，用List.Zip【订单号】、【购买内容】和【价格(总价)】列进行融合

得出来的每个列表的内容如下（以第一行为例）

随后，直接对生成的列进行拓展

再通过提取值(事实上就是把List里面的各项元素通过特定的分隔符连接起来）

随后再对此列使用拆分列按行拆分即可

后续就是删除重复的列、重命名跟排序的工作啦，完成之后就得到了想要的结果

高级编辑器懒人代码，复制输入即可

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    更改的类型 = Table.TransformColumnTypes(源,{{"编号", Int64.Type}, {"购买人", type text}, {"地址", type text}, {"手机号", type text}, {"订单号", type text}, {"购买内容", type text}, {"价格(总价)", Int64.Type}}),
    拆分对应列到List = Table.TransformColumns(更改的类型,{{"订单号",each Text.SplitAny(_,",")},{"购买内容",each Text.SplitAny(_,",#(lf)")}}),
    合并多列 = Table.AddColumn(自定义1,"应用列",each List.Zip({[订单号],[购买内容],{[#"价格(总价)"]}})),
    #"展开的“应用列”" = Table.ExpandListColumn(自定义2, "应用列"),
    提取值 = Table.TransformColumns(#"展开的“应用列”", {"应用列", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    按分隔符拆分列 = Table.SplitColumn(提取值, "应用列", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"应用列.1", "应用列.2", "应用列.3"}),
    更改的类型1 = Table.TransformColumnTypes(按分隔符拆分列,{{"应用列.1", type text}, {"应用列.2", type text}, {"应用列.3", Int64.Type}}),
    删除的列 = Table.RemoveColumns(更改的类型1,{"订单号", "购买内容", "价格(总价)"}),
    重命名的列 = Table.RenameColumns(删除的列,{{"应用列.1", "订单号"}, {"应用列.2", "购买内容"}, {"应用列.3", "价格"}})
in
    重命名的列