let
源 = Excel.CurrentWorkbook(){[Name="Case1"]}[Content],
自定义1 = Table.Combine(
Table.Group(源, "语文|数学|英语", {"q", each
[ a = List.RemoveNulls(List.Combine(Table.ToColumns())),
b = List.Combine(List.Transform(Table.ColumnNames(), (x)=> Text.Split(x, "|"))),
c = #table(b, {a})
][c]
}, 0, (x,y)=> Number.From(y is null)
)[q]
)
in
自定义1

let
源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
合并 = Table.CombineColumns(源,Table.ColumnNames(源),each Text.Combine(,"-"),"合并"),
分组 = Table.Combine(
Table.Group(合并,"合并",
{"a",each
Table.FromColumns(
List.Transform(
List.Combine(List.Transform([合并],(x)=>Text.Split(x,"-"))),
each {})
)
},
0,(x,y)=>Number.From(Text.StartsWith(y,"3"))
)[a])
in
分组

参考思路：用Table.Group的第五个参数进行分组，再把合并的列拆分，整合成表即可。

let
源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
分组合并 = Table.Combine(
Table.Group(源,{"条件1","条件2"},{"Q",each
[a=[累计值],
b={a{0}}&List.Transform({1..List.Count(a)-1},(x)=>a{x}-a{x-1}),
c=Table.FromColumns(Table.ToColumns()&{b},Table.ColumnNames()&{"当月值"})
][c]})[Q])
in
分组合并

let
原数据 = Excel.CurrentWorkbook(){[Name="原数据"]}[Content],
词根表 = Excel.CurrentWorkbook(){[Name="词根表"]}[Content],
添加列 = Table.AddColumn(原数据,"a",each
[a=Table.ToColumns(词根表),
b=List.Transform({0..List.Count(a)-1},(y)=>try List.Select(List.Distinct(a{y}),(x)=>Text.Contains([列1],x)) otherwise null),
c=Table.FromColumns(b,Table.ColumnNames(词根表))
][c]),
展开列 = Table.ExpandTableColumn(添加列,"a",Table.ColumnNames(添加列[a]{0}))
in
展开列