员工打卡记录的数据提取整理

chenwenyong

file:///C:\Users\Mitu\AppData\Roaming\Tencent\Users\352166534\QQ\WinTemp\RichOle\(TJN%@P9@YHNY0V~DN`7VVH.pngfile:///C:\Users\Mitu\AppData\Roaming\Tencent\Users\352166534\QQ\WinTemp\RichOle\5BLGZI4TTN4UY6_1WF(JC25.png这是群友提出的数据整理须求。之所以费神研究，纯粹是无聊。在这里发贴也仅是作一个成品存储之地，或许以后有须要之时。顺便也让有兴趣的人共同研究。
过程及思路：根据原始数据规律把数据分割成“工号，日期，时间”的列表，这里要用到的函数主要有List.PositionOf，List.InsertRange，List.TransformMany，List.Range; 提取完毕后，再生成“工号，姓名，部门，日期，时间”的记录列表，这里要用到的函数有List.Transform，List.Zip，List.Range，以及从列到记录，从记录到表，各类展开等函数，具体看源文件。不做过多详细解释。

libo5563

晕，居然没存盘，没劲再写一遍了，字段名要修改一下

chenwenyong

libo5563 发表于 2020-11-15 22:04
晕，居然没存盘，没劲再写一遍了，字段名要修改一下

还是个半成品，日期成了第一次打卡，时间没有分列，公式不完整！ 既然出手了就再麻烦兄弟，把公式整完善一下，也供以后或许需要的人省点力气！

deadzlq

PowerQuery参考解法

libo5563

工号19，郭强8-11日5次打卡记录

shaowu459

一种方法供参考，，，

larasmile

有没有M函数进阶的介绍哦，看的好费劲哦，想系统的学习下。

chenwenyong

更新一个 let     源 = Excel.Workbook(File.Contents("C:\Users\Mitu\Downloads\员工刷卡记录表9月\员工刷卡记录表9月.xlsx"), null, true)[Data]{0},     只保留月份内行 = Table.SelectColumns(源, List.Transform({1..31},each "Column" & Text.From(_))),  //去除31号后的空列     工号位置 = List.PositionOf(只保留月份内行[Column1],"工号：",2),  //工号所在行位置     补足工号位数 = List.InsertRange(工号位置,List.Count(工号位置),{List.Last(工号位置)+4}),  //给最后一个工号加2行     员工信息 = Table.SelectColumns(Table.SelectRows(源, each ([Column1] = "工号：")), List.Transform({"1","3","10","11","17","18"},each "Column" & _)),     添加索引 = Table.AddIndexColumn(员工信息, "索引", 0, 1, Int64.Type),     添加考勤信息 = Table.AddColumn(添加索引, "添加考勤信息", each Table.Transpose(Table.Range(只保留月份内行,补足工号位数{_[索引]}+1,补足工号位数{_[索引] +1}-补足工号位数{_[索引]}-1))),  //取得考勤信息     展开考勤信息 = Table.ExpandTableColumn(添加考勤信息, "添加考勤信息", {"Column1", "Column2", "Column3"}, {"添加考勤信息.Column1", "添加考勤信息.Column2", "添加考勤信息.Column3"}),     拆分1 = Table.SplitColumn(展开考勤信息, "添加考勤信息.Column2", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"添加考勤信息.Column2.1", "添加考勤信息.Column2.2"}),     拆分2 = Table.SplitColumn(拆分1, "添加考勤信息.Column3", Splitter.SplitTextByDelimiter("#(#)(lf)", QuoteStyle.Csv), {"添加考勤信息.Column3.1", "添加考勤信息.Column3.2"}),     删除的列 = Table.RemoveColumns(拆分2,{"Column1","Column10","Column17","Column18","索引"}),     重命名的列 = Table.RenameColumns(删除的列,{{"Column3", "工号"}, {"Column11", "姓名"}, {"添加考勤信息.Column1", "日期"}, {"添加考勤信息.Column2.1", "时间01"}, {"添加考勤信息.Column2.2", "时间02"}, {"添加考勤信息.Column3.1", "时间03"}, {"添加考勤信息.Column3.2", "时间04"}}) in     重命名的列

chenwenyong

let
    源 = Excel.Workbook(File.Contents("C:\Users\Mitu\Downloads\员工刷卡记录表9月\员工刷卡记录表9月.xlsx"), null, true)[Data]{0},
    只保留月份内行 = Table.SelectColumns(源, List.Transform({1..31},each "Column" & Text.From(_))),  //去除31号后的空列
    工号位置 = List.PositionOf(只保留月份内行[Column1],"工号：",2),  //工号所在行位置
    补足工号位数 = List.InsertRange(工号位置,List.Count(工号位置),{List.Last(工号位置)+4}),  //给最后一个工号加2行
    员工信息 = Table.SelectColumns(Table.SelectRows(源, each ([Column1] = "工号：")), List.Transform({"1","3","10","11","17","18"},each "Column" & _)),
    添加索引 = Table.AddIndexColumn(员工信息, "索引", 0, 1, Int64.Type),
    添加考勤信息 = Table.AddColumn(添加索引, "添加考勤信息", each Table.Transpose(Table.Range(只保留月份内行,补足工号位数{_[索引]}+1,补足工号位数{_[索引] +1}-补足工号位数{_[索引]}-1))),  //取得考勤信息
    展开考勤信息 = Table.ExpandTableColumn(添加考勤信息, "添加考勤信息", {"Column1", "Column2", "Column3"}, {"添加考勤信息.Column1", "添加考勤信息.Column2", "添加考勤信息.Column3"}),
    拆分1 = Table.SplitColumn(展开考勤信息, "添加考勤信息.Column2", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"添加考勤信息.Column2.1", "添加考勤信息.Column2.2"}),
    拆分2 = Table.SplitColumn(拆分1, "添加考勤信息.Column3", Splitter.SplitTextByDelimiter("#(#)(lf)", QuoteStyle.Csv), {"添加考勤信息.Column3.1", "添加考勤信息.Column3.2"}),
    删除的列 = Table.RemoveColumns(拆分2,{"Column1","Column10","Column17","Column18","索引"}),
    重命名的列 = Table.RenameColumns(删除的列,{{"Column3", "工号"}, {"Column11", "姓名"}, {"添加考勤信息.Column1", "日期"}, {"添加考勤信息.Column2.1", "时间01"}, {"添加考勤信息.Column2.2", "时间02"}, {"添加考勤信息.Column3.1", "时间03"}, {"添加考勤信息.Column3.2", "时间04"}})
in
    重命名的列