let
源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
自定义1 = [a=Table.SelectRows(源,each Text.Contains(Text.From([列1]),"型号")),b=List.Transform(Table.ToRows(a),each List.Transform(List.FirstN(_,4),each Text.AfterDelimiter(_,":"))),c=Table.FromRows(b,{"型号","名称","颜色","总支数"})][c],
自定义2 = Table.AddColumn(自定义1,"单支长度",each Table.PromoteHeaders(Table.Skip(Table.Group(源,"列1",{"分组",each _},0,(x,y)=> Number.From(Text.Contains(Text.From(if y=null then "" else y),"型号"))){[列1="型号:"&[型号]]}[分组]))),
自定义3 = Table.TransformColumns(自定义2,{"单支长度",each [a=Table.Group(_,"订料长度",{{"长度",each List.Sum(List.Transform([开料方式],each Expression.Evaluate(_)))},{"数量",each List.Max([需求数量])}},0,(x,y)=>Number.From( y is number)),b=Table.Group(a,"长度",{"数量",each List.Sum([数量])}),c=Table.Sort(b,{"长度",1}),d=Table.AddColumn(c,"文本组合",each Text.From([长度])&"*"&Text.From([数量])),e=Text.Combine(d[文本组合],"+")][e]})
in
自定义3
|
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2023-8-1 22:57
楼主
