搞错了,是下面这个,也是最后一行显示2,而不是空格。
let
源 = Excel.CurrentWorkbook(){[Name="表2"]}[Content],
分组 = Table.Group(源,"工号",{"a",(x)=>Table.Group(x,"进出标志",{"b",each _},0)}),
排列1 = List.Transform(分组[a],(x)=> List.Combine(List.Transform(x[b],each if [进出标志]{0}="出" then {1}&List.Repeat({null},List.Count([进出标志])-1) else List.Repeat({null},List.Count([进出标志])-1)&{2}))),
排列2 = List.Combine(List.Transform(排列1,(x)=> try if List.PositionOf(x,1)<>0 then {0}&List.Skip(x,List.PositionOf(x,1)) else x otherwise {0}&List.Repeat({null},List.Count(x)-1))),
转表 = Table.FromColumns(Table.ToColumns(源)&{排列2},Table.ColumnNames(源)&{"号码"})
in
转表 |