Forum Discussion
Solved
Help merging rows based on Time Constraints!
Ok so I have had a steep Power Query learning curve recently, but I have hit a wall and would appreciate any help getting this last bit over the line!! I receive a CSV file of people clocking in and...
No worries, that's what I'm here for 🙂
Try this code instead:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZJNC4JAEIb/ingOnK9d2712CgK7iwcRKQkU1P5/K9Rhs5bV08wwzMPD8JZlem3HaegTTA8pITED5sa4gTEDlREQuQG0hdzVYrzVfTfVc/c+Od3b5pEUz9n1l6H5LKpDLFhZMf/B534nl9iqgLDPpSAXHFdH+n49wgMv56JEGzFK8h+PYIwV5ngukoVjtPAWMAfBvrBs4OotwhIfCQQLsCtqK2EvEotwgLsWrl4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, ID = _t, Date = _t, Time = _t, #"Person Group" = _t, Status = _t, #"Attendance Check Point" = _t]), chgTypes = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time", type time}}), addNextStatusExpected = Table.AddColumn(chgTypes, "nextStatusExpected", each if [Status] = "Check Out" then "Check In" else "Check Out"), addDateTime = Table.AddColumn(addNextStatusExpected, "dateTime", each [Date] & [Time], type datetime), sort_ID_dateTime = Table.Sort(addDateTime,{{"ID", Order.Ascending}, {"dateTime", Order.Ascending}}), addIndex1 = Table.AddIndexColumn(sort_ID_dateTime, "Index1", 1, 1, Int64.Type), addIndex0 = Table.AddIndexColumn(addIndex1, "Index0", 0, 1, Int64.Type), mergeIndex1Index0 = Table.NestedJoin(addIndex0, {"ID", "Index1", "Status"}, addIndex0, {"ID", "Index0", "nextStatusExpected"}, "addIndex0", JoinKind.LeftOuter), expandIndex1Index0 = Table.ExpandTableColumn(mergeIndex1Index0, "addIndex0", {"dateTime", "Index1"}, {"addIndex0.dateTime", "addIndex0.Index1"}), filterRedundantRows = Table.SelectRows(expandIndex1Index0, each ([Status] = "Check In") or ([Status] = "Check Out" and [addIndex0.dateTime] = null and not List.Contains(List.Buffer(expandIndex1Index0[addIndex0.Index1]), [Index1]))), remOthCols = Table.SelectColumns(filterRedundantRows,{"Name", "ID", "Person Group", "Status", "Attendance Check Point", "dateTime", "addIndex0.dateTime"}), addTimeWorked = Table.AddColumn(remOthCols, "timeWorked", each [addIndex0.dateTime] - [dateTime]) in addTimeWorkedThere's two key changes:
1) I added a new [nextStatusExpected] column at step 1, and I use this in the merge to force a blank record to be generated if there's two check outs or check ins in a row.
2) I've added a step called 'filterRedundantRows' which replaces our previous step 6. This is really where the smart stuff is done working out whether a row is the result of a missed check in/out, or whether it's a redundant row that's already been matched correctly.
I now get this output based on amended input data (added a double check in for person 1, and a double check out for person 2):
Let me know how you get on.
Pete
Thanks for the quick response Pete.
I'm hoping it's easy and just the perils of self-teaching! You don't know what you don't know!
let
Source = Folder.Files("K:\Quality\DATA SOURCES\04 - Spencer Facial\Raw"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type any}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each not Text.Contains([Column1], "The Spencer Group")),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each not Text.Contains([Column1], "Raw Record Report")),
#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each not Text.Contains([Column1], "Total")),
#"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows2", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Attendance_Raw Record Report_20220507_20220513.csv", type text}, {"Name#(tab)", type text}, {"ID#(tab)", type text}, {"Skin-Surface Temperature#(tab)", type text}, {"Skin-Surface Temperature Status#(tab)", type text}, {"Date#(tab)", type text}, {"Time#(tab)", type text}, {"Day of Week#(tab)", type text}, {"Person Group#(tab)", type text}, {"Status#(tab)", type text}, {"Attendance Check Point#(tab)", type text}, {"Customized Attendance Status#(tab)", type text}, {"Data Source#(tab)", type text}, {"Handling Type#(tab)", type text}, {"", type any}}),
#"Filtered Rows3" = Table.SelectRows(#"Changed Type1", each not Text.Contains([#"Name#(tab)"], "name")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows3",{"Customized Attendance Status#(tab)", "Data Source#(tab)", "Handling Type#(tab)", "", "Skin-Surface Temperature#(tab)", "Skin-Surface Temperature Status#(tab)", "Attendance_Raw Record Report_20220507_20220513.csv"}),
#"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"ID#(tab)", Text.Trim, type text}, {"Date#(tab)", Text.Trim, type text}, {"Time#(tab)", Text.Trim, type text}, {"Day of Week#(tab)", Text.Trim, type text}, {"Person Group#(tab)", Text.Trim, type text}, {"Status#(tab)", Text.Trim, type text}, {"Attendance Check Point#(tab)", Text.Trim, type text}})
in
#"Trimmed Text"