需求
如下图示，同事传来一个表格，有1200多行，课程序号、系列和课程名称均是合并单元格（截图中我已经打散），时间列包含“11|13”（分|秒）、“——”（无时间数据）和“4.5”（分钟）这样的数字。
要求整理成如下格式：
将每个课程下面的若干课件合并到一行，用“|”隔开；
将每个课程若干课件的时间累加，并处理成“分|秒”格式，如下图：
分析
首先需要将合并单元格打散，这个引入数据源之后，PowerQuery会自动拆分合并单元格；
其次要将原来合并单元格拆散后留下的空白单元格填充对应的内容，在课程序号、课程序列和课程名称三个列用“向下填充”即可。
然后要将时间列按照“|”拆分出分钟和秒钟来。
利用分组（group by）功能将同一个课程的若干课件放到一个嵌套表格中，并计算该课程各课件的时间之和。
把嵌套表格中的各行用“|”串起来，形成与课程序号、课程系列和课程名称同行的一行。
用“|”处理时间。
我主要卡在第五步。后来终于试验摸索出来。最后形成了两套代码。
摸索版代码
let
源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
删除的列 = Table.RemoveColumns(源,{"形式", "列1"}),
向下填充 = Table.FillDown(删除的列,{"课程序号", "系列", "课程名称"}),
按分隔符拆分列 = Table.SplitColumn(Table.TransformColumnTypes(向下填充, {{"时间", type text}}, "zh-CN"), "时间", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"时间.1", "时间.2"}),
按分隔符拆分列1 = Table.SplitColumn(按分隔符拆分列, "时间.1", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"时间.1.1", "时间.1.2"}),
更改的类型 = Table.TransformColumnTypes(按分隔符拆分列1,{{"时间.1.2", Int64.Type}, {"时间.2", Int64.Type}}),
已添加自定义 = Table.AddColumn(更改的类型, "自定义", each [时间.1.2]/10*60),
删除的列1 = Table.RemoveColumns(已添加自定义,{"时间.1.2"}),
合并的列 = Table.CombineColumns(Table.TransformColumnTypes(删除的列1, {{"时间.2", type text}, {"自定义", type text}}, "zh-CN"),{"时间.2", "自定义"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"秒"),
重命名的列 = Table.RenameColumns(合并的列,{{"时间.1.1", "分"}}),
更改的类型1 = Table.TransformColumnTypes(重命名的列,{{"分", Int64.Type}, {"秒", Int64.Type}}),
分组的行 = Table.Group(更改的类型1, {"课程序号", "系列", "课程名称"}, {{"分", each List.Sum([分]), type text}, {"秒", each List.Sum([秒]), type text}, {"返回所有行", each _, type table}}),
替换的值 = Table.ReplaceValue(分组的行,null,"0",Replacer.ReplaceValue,{"秒"}),
更改的类型2 = Table.TransformColumnTypes(替换的值,{{"分", Int64.Type}, {"秒", Int64.Type}}),
已添加自定义1 = Table.AddColumn(更改的类型2, "新分", each if [秒]>60 then [分]+Number.RoundDown([秒]/60) else[分]),
已添加自定义2 = Table.AddColumn(已添加自定义1, "新秒", each if [秒]>60 then [秒]-([新分]-[分])*60 else [秒]),
删除的列2 = Table.RemoveColumns(已添加自定义2,{"分", "秒"}),
合并的列1 = Table.CombineColumns(Table.TransformColumnTypes(删除的列2, {{"新分", type text}, {"新秒", type text}}, "zh-CN"),{"新分", "新秒"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"时间"),
已添加自定义3 = Table.AddColumn(合并的列1, "自定义", each Table.SelectColumns([返回所有行],"课件名称")),
已添加自定义4 = Table.AddColumn(已添加自定义3, "自定义.1", each [自定义][课件名称]),
提取值 = Table.TransformColumns(已添加自定义4, {"自定义.1", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
删除的列3 = Table.RemoveColumns(提取值,{"返回所有行", "自定义"}),
重排序的列 = Table.ReorderColumns(删除的列3,{"课程序号", "系列", "课程名称", "自定义.1", "时间"})
in
重排序的列
但是我觉得这个处理方式太笨拙，因为我不怎么会用Table.TransformColumns()和“_”，而且明显对函数不熟。强迫自己继续学习，最终形成了更简洁的第二套代码。
简化版代码
let
源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
删除的列 = Table.RemoveColumns(源,{"形式", "列1"}),
向下填充 = Table.FillDown(删除的列,{"课程序号", "系列", "课程名称"}),
更改的类型 = Table.TransformColumnTypes(向下填充,{{"时间", type text}}),
时间换算为秒 = Table.TransformColumns(更改的类型,
{{
"时间",
each if
Text.Contains(_,"|")
then
Number.From(Text.Start(_,Text.PositionOf(_,"|")))*60+Number.From(Text.End(_,Text.Length(_)-1-Text.PositionOf(_,"|")))
else
Number.From(_)*60
}}
),
同一个课程的课件聚合 = Table.Group(时间换算为秒, {"课程序号", "系列", "课程名称"}, {{"时间", each List.Sum([时间]), type number}, {"返回行", each Table.SelectColumns(_,"课件名称"), type table}}),
提取课件名称列 = Table.TransformColumns(同一个课程的课件聚合, {"返回行", each Text.Combine(List.Transform(_[课件名称], Text.From), "|"), type text}),
重排序的列 = Table.ReorderColumns(提取课件名称列,{"课程序号", "系列", "课程名称", "返回行", "时间"}),
#"把时间转换成“分|秒”格式" = Table.TransformColumns(重排序的列,{"时间",each Number.ToText(Number.IntegerDivide(_,60))&"|"&Number.ToText(Number.Mod(_,60))})
in
#"把时间转换成“分|秒”格式"
方法二要复杂些，但相对要简单些，而且不容易出错。事实上，我用代码一摸索的时候，时间计算就出了错，最后得出了27|60这样的结果。
反思
自己的主要弱项在于：一是对函数不熟，二是很不会用Table.TransformColumns()和“”，往往只会笨拙地通过添加列来实现目的。所以，通过这个例子，无疑强化了自己对列变换、each和“”的熟练程度。
是为记。
