let
源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
添加姓名列 = Table.AddColumn(源, "姓名", each if [列10] = "姓名：" then [列5] & [列11] else null),
姓名列填充 = Table.FillDown(添加姓名列,{"姓名"}),
筛选掉不需要的行 = Table.SelectRows(姓名列填充, each ([列4] <> 4 and [列4] <> "工号：") and ([姓名] <> null)),
按姓名分组组内整理 = Table.Group(筛选掉不需要的行, {"姓名"}, {"内容", each Table.SelectRows(Table.Transpose(Table.DemoteHeaders(_)), each ([Column1] <> "姓名"))}),
添加序号列 = Table.AddIndexColumn(按姓名分组组内整理, "序号", 1),
展开11列 = Table.ExpandTableColumn(添加序号列, "内容", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6","Column7", "Column8", "Column9", "Column10", " Column11"}),
打卡时间转成一列 = Table.UnpivotOtherColumns(展开11列, {"姓名", "Column1", "序号"}, "属性", "打卡时间"),
打卡时间一行一时间 = Table.TransformColumnTypes(Table.ExpandListColumn(Table.TransformColumns(打卡时间转成一列, {{"打卡时间", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "打卡时间"), {"打卡时间", type time}),
按姓名日期分组组内整理 = Table.Group(打卡时间一行一时间, {"姓名", "Column1"}, {"内容", each Table.AddIndexColumn(_, "次数", 1)}),
展开 = Table.ExpandTableColumn(按姓名日期分组组内整理, "内容", {"序号", "打卡时间", "次数"}),
打卡时间横排 = Table.Pivot(Table.TransformColumnTypes(展开, {{"次数", type text}}, "zh-CN"), List.Distinct(Table.TransformColumnTypes(展开, {{"次数", type text}}, "zh-CN")[次数]), "次数", "打卡时间"),
整理日期列 = Table.TransformColumnTypes( Table.RenameColumns(Table.ReplaceValue(打卡时间横排,"列","2023-10-",Replacer.ReplaceText,{"Column1"}), {"Column1", "日期"}),{"日期", type date}),
整理顺序 = Table.RemoveColumns(Table.Sort(整理日期列,{{"序号", Order.Ascending}, {"日期", Order.Ascending}}), {"序号"})
in
整理顺序