Fill-in missing time gaps

Nick Burns

Nick Burns

Member
Joined
May 24, 2017
Messages
162
Reaction score
0
Points
16
Excel Version(s)
Office 365
I have data that looks like the table on the left. Some missing Straight Time (ST), some missing Over and Double Time (OT/DT).


MissingTime.PNG

Need to make sure that there is each time rate for each employee and analysis (Job/Section/Cost Head).
Employees may enter the same Analysis twice as seen for the first employee. Need to make sure those are two separate groupings as well.

Not exactly sure how to tackle this

Attached is the starting table.
View attachment MissingTime.xlsx

Knowing me, I'll come up with a solution 20min after posting this :) - will post my solution if I do.
 
I'm hoping your answer to this question will answer several things at once for me.
If, at the bottom of your tblhrs table (on the sheet) the last three lines were changed to:
2022-05-05_121700.png
Here I've just swapped ST and DT values (cells with red comment triangles)
Should these rows be treated as one set of ST,OT,DT and just sorted
or
the first DT should be treated as the last of one ST,OT,DT set, the OT treated as the middle of another ST,OT,DT set and the ST at the bottom treated as the start of a third ST,OT,DT set?
Maybe you'll tell me this could never happen?
 
p45cal said:
I'm hoping your answer to this question will answer several things at once for me.
If, at the bottom of your tblhrs table (on the sheet) the last three lines were changed to:
View attachment 10922
Here I've just swapped ST and DT values (cells with red comment triangles)
Should these rows be treated as one set of ST,OT,DT and just sorted
or
the first DT should be treated as the last of one ST,OT,DT set, the OT treated as the middle of another ST,OT,DT set and the ST at the bottom treated as the start of a third ST,OT,DT set?
Maybe you'll tell me this could never happen?
Click to expand...
The final output should always be in the order of ST,OT,DT. That being said, this is being entered by another employee and though they should be entering it in that order, I can't guarantee that. They should also be providing all three time rates, but I'm trying to make it error-proof as much as possible.
 
Hi Nick,

my solution needs only one Query.
 

Attachments

  • xlguru - Fill-in missing time gaps (PQ).xlsx
    30.1 KB · Views: 3
Hello Nick,

if you use my test data as input for your solution and update the result, you will notice some differences. I may be wrong, but in my opinion the results of your solution are not correct.

The number of rows must always be a multiple of 3. And 55 is not a multiple of 3.
 
Last edited:
pinarello said:
Hello Nick,

if you use my test data as input for your solution and update the result, you will notice some differences. I may be wrong, but in my opinion the results of your solution are not correct.

The number of rows must always be a multiple of 3. And 55 is not a multiple of 3.
Click to expand...

Thanks - I'll push your data through my solution to see where the logic fails.

You have a very interesting approach.

I stepped through it and tightened up some of the code - this is only as a means of understanding what you did.

Such things I did was Grouping and Indexing in a single step.
I also added/expanded a List of the RateTypes instead of doing multiple Appends/Replaces.

Here's what I came up with using your code:
Code: 
let    
    Source                    = Excel.CurrentWorkbook(){[Name="tblHours"]}[Content],
    Change_Type               = Table.TransformColumnTypes(Source,{{"EmpNum", Text.Type}, {"WeekEnding", type date}, {"Job Number", type text}, {"Section", type text}, {"Cost Head", type text}, {"Rate", type text}, {"SAT", Int64.Type}, {"SUN", Int64.Type}, {"MON", Int64.Type}, {"TUE", Int64.Type}, {"WED", Int64.Type}, {"THU", Int64.Type}, {"FRI", Int64.Type}}),
    GroupI                    = Table.Group(Change_Type, {"EmpNum", "WeekEnding", "Job Number", "Section", "Cost Head", "Rate"}, {{"i", each Table.AddIndexColumn(_, "i", 1, 1, Int64.Type), Table.Type}}),
    Col_idx                   = Table.AddIndexColumn(GroupI, "idx", 1, 1),
    Expand_i                  = Table.ExpandTableColumn(Col_idx, "i", {"SAT", "SUN", "MON", "TUE", "WED", "THU", "FRI", "i"}, {"SAT", "SUN", "MON", "TUE", "WED", "THU", "FRI", "i.1"}),
    Col_Prio                  = Table.AddColumn(Expand_i, "Prio", each 1),
    Group_Rows2               = Table.Group(Col_Prio, {"EmpNum", "WeekEnding", "Job Number", "Section", "Cost Head", "i.1"}, {{"All", each _, type table [EmpNum=nullable text, WeekEnding=nullable date, Job Number=nullable text, Section=nullable text, Cost Head=nullable text, Rate=nullable text, SAT=nullable number, SUN=nullable number, MON=nullable number, TUE=nullable number, WED=nullable number, THU=nullable number, FRI=nullable number, i.1=number, idx=number, Prio=number]}}),
    Col_i2                    = Table.AddIndexColumn(Group_Rows2, "i2", 1, 1),
    Analysis                  = Table.RemoveColumns(Col_i2,{"All"}),
    HoursData                 = Col_i2,
    AppendAnalysisToHoursData = Table.Combine({Analysis, HoursData}),
    AddRateTypes              = Table.AddColumn(AppendAnalysisToHoursData, "Rate2", each if [All] = null then {"ST","OT","DT"} else null),
    ExpandRateTypes           = Table.ExpandListColumn(AddRateTypes, "Rate2"),
    Expand_All                = Table.ExpandTableColumn(ExpandRateTypes, "All", {"Rate", "SAT", "SUN", "MON", "TUE", "WED", "THU", "FRI", "Prio"}, {"Rate", "SAT", "SUN", "MON", "TUE", "WED", "THU", "FRI", "Prio"}),
    Replace_Prio              = Table.ReplaceValue(Expand_All,null,2,Replacer.ReplaceValue,{"Prio"}),
    ReplaceRate               = Table.ReplaceValue(Replace_Prio,each [Rate], each if [Rate] = null then  [Rate2] else [Rate], Replacer.ReplaceValue, {"Rate"}),
    Sort_Table                = Table.Buffer(Table.Sort(ReplaceRate,{{"i2", Order.Ascending}, {"Rate", Order.Descending}, {"Prio", Order.Ascending}})),
    GroupI3                   = Table.Group(Sort_Table, {"i2", "Rate"}, {{"i3", each Table.AddIndexColumn(_, "i3", 1, 1, Int64.Type), Table.Type}}),
    Expand_i3                 = Table.ExpandTableColumn(GroupI3, "i3", {"EmpNum", "WeekEnding", "Job Number", "Section", "Cost Head", "SAT", "SUN", "MON", "TUE", "WED", "THU", "FRI", "i3"}, {"EmpNum", "WeekEnding", "Job Number", "Section", "Cost Head", "SAT", "SUN", "MON", "TUE", "WED", "THU", "FRI", "i3"}),
    Filter_i3_1               = Table.SelectRows(Expand_i3, each ([i3] = 1)),
    Keep_needed_Cols          = Table.SelectColumns(Filter_i3_1,{"EmpNum", "WeekEnding", "Job Number", "Section", "Cost Head", "Rate", "SAT", "SUN", "MON", "TUE", "WED", "THU", "FRI"}),
    RestoreTypes              = Value.ReplaceType(Keep_needed_Cols,Value.Type(Change_Type))
in
    RestoreTypes
Technically, the HoursData step can be removed and just use Col_i2 ;)

One question - what does Prio mean? I thought it meant Prior, but I'm not so sure.

Thanks again for your solution! :cheer2:
 
Hello Nick,

thanks to your optimisation, which I had already thought of but didn't know how easy it would be to implement, I have now been able to optimise the query so that records are only inserted if a recordset is not complete.

Since only a few records are usually missing, this will certainly have an effect on the runtime if there is a large amount of data.

See here the new code:
Code: 
let    
    Source                    = Excel.CurrentWorkbook(){[Name="tblHours"]}[Content],
    Change_Type               = Table.TransformColumnTypes(Source,{{"EmpNum", Text.Type}, {"WeekEnding", type date}, {"Job Number", type text}, {"Section", type text}, {"Cost Head", type text}, {"Rate", type text}, {"SAT", Int64.Type}, {"SUN", Int64.Type}, {"MON", Int64.Type}, {"TUE", Int64.Type}, {"WED", Int64.Type}, {"THU", Int64.Type}, {"FRI", Int64.Type}}),
    GroupI                    = Table.Group(Change_Type, {"EmpNum", "WeekEnding", "Job Number", "Section", "Cost Head", "Rate"}, {{"i", each Table.AddIndexColumn(_, "i", 1, 1, Int64.Type), Table.Type}}),
    Col_idx                   = Table.AddIndexColumn(GroupI, "idx", 1, 1),
    Expand_i                  = Table.ExpandTableColumn(Col_idx, "i", {"SAT", "SUN", "MON", "TUE", "WED", "THU", "FRI", "i"}, {"SAT", "SUN", "MON", "TUE", "WED", "THU", "FRI", "i.1"}),
    Col_Prio                  = Table.AddColumn(Expand_i, "Prio", each 1),
    Group_Rows2               = Table.Group(Col_Prio, {"EmpNum", "WeekEnding", "Job Number", "Section", "Cost Head", "i.1"}, {{"All", each _, type table [EmpNum=nullable text, WeekEnding=nullable date, Job Number=nullable text, Section=nullable text, Cost Head=nullable text, Rate=nullable text, SAT=nullable number, SUN=nullable number, MON=nullable number, TUE=nullable number, WED=nullable number, THU=nullable number, FRI=nullable number, i.1=number, idx=number, Prio=number]}, {"No.Entries", each Table.RowCount(_), Int64.Type}}),
    Col_i2                    = Table.AddIndexColumn(Group_Rows2, "i2", 1, 1),
    Analysis                  = Table.RemoveColumns(Col_i2,{"All"}),
    Remove_No.Entries         = Table.RemoveColumns(Col_i2,{"No.Entries"}),
    AppendAnalysisToHoursData = Table.Combine({Analysis, Remove_No.Entries}),
    Filter_relevant           = Table.SelectRows(AppendAnalysisToHoursData, each ([No.Entries] <> 3)),
    AddRateTypes              = Table.AddColumn(Filter_relevant, "Rate2", each if [All] = null then {"ST","OT","DT"} else null),
    ExpandRateTypes           = Table.ExpandListColumn(AddRateTypes, "Rate2"),
    Expand_All                = Table.ExpandTableColumn(ExpandRateTypes, "All", {"Rate", "SAT", "SUN", "MON", "TUE", "WED", "THU", "FRI", "Prio"}, {"Rate", "SAT", "SUN", "MON", "TUE", "WED", "THU", "FRI", "Prio"}),
    Replace_Prio              = Table.ReplaceValue(Expand_All,null,2,Replacer.ReplaceValue,{"Prio"}),
    ReplaceRate               = Table.ReplaceValue(Replace_Prio,each [Rate], each if [Rate] = null then  [Rate2] else [Rate], Replacer.ReplaceValue, {"Rate"}),
    Sort_Table                = Table.Buffer(Table.Sort(ReplaceRate,{{"i2", Order.Ascending}, {"Rate", Order.Descending}, {"Prio", Order.Ascending}})),
    GroupI3                   = Table.Group(Sort_Table, {"i2", "Rate"}, {{"i3", each Table.AddIndexColumn(_, "i3", 1, 1, Int64.Type), Table.Type}}),
    Expand_i3                 = Table.ExpandTableColumn(GroupI3, "i3", {"EmpNum", "WeekEnding", "Job Number", "Section", "Cost Head", "SAT", "SUN", "MON", "TUE", "WED", "THU", "FRI", "i3"}, {"EmpNum", "WeekEnding", "Job Number", "Section", "Cost Head", "SAT", "SUN", "MON", "TUE", "WED", "THU", "FRI", "i3"}),
    Filter_i3_1               = Table.SelectRows(Expand_i3, each ([i3] = 1)),
    Keep_needed_Cols          = Table.SelectColumns(Filter_i3_1,{"EmpNum", "WeekEnding", "Job Number", "Section", "Cost Head", "Rate", "SAT", "SUN", "MON", "TUE", "WED", "THU", "FRI"}),
    RestoreTypes              = Value.ReplaceType(Keep_needed_Cols,Value.Type(Change_Type))
in
    RestoreTypes

And it shows once again that those who share knowledge learn more and faster than those who make a secret of their knowledge.

With this in mind, I wish everyone a nice weekend and lots more fun with Power Query.
 
Thanks again for those changes!
Along the same lines, I moved the No.Entries filter prior to the append so that less data is being appended.
I also moved the AddRates prior to the filter, after the new filter step (this becomes the new Analysis to be appended). It's just an Add Column with the list, so no if/then eval. The expanded list still occurs after the append.
 
Now I have changed the query so that only the missing records are inserted directly. This means that data records are not inserted first, which then have to be deleted afterwards, and in total there are now even fewer steps.

Code: 
let    
    Source                    = Excel.CurrentWorkbook(){[Name="tblHours"]}[Content],
    Change_Type               = Table.TransformColumnTypes(Source,{{"EmpNum", Text.Type}, {"WeekEnding", type date}, {"Job Number", type text}, {"Section", type text}, {"Cost Head", type text}, {"Rate", type text}, {"SAT", Int64.Type}, {"SUN", Int64.Type}, {"MON", Int64.Type}, {"TUE", Int64.Type}, {"WED", Int64.Type}, {"THU", Int64.Type}, {"FRI", Int64.Type}}),
    Col_mKey                  = Table.AddColumn(Change_Type, "mKey", each if [Rate] = "ST" then 1 else if [Rate] = "OT" then 2 else 4),
    GroupI                    = Table.Group(Col_mKey, {"EmpNum", "WeekEnding", "Job Number", "Section", "Cost Head", "Rate"}, {{"i", each Table.AddIndexColumn(_, "i", 1, 1), Table.Type}}),
    Col_idx                   = Table.AddIndexColumn(GroupI, "idx", 1, 1),
    Expand_i                  = Table.ExpandTableColumn(Col_idx, "i", {"SAT", "SUN", "MON", "TUE", "WED", "THU", "FRI", "mKey", "i"}, {"SAT", "SUN", "MON", "TUE", "WED", "THU", "FRI", "mKey", "i.1"}),
    Group_Rows2               = Table.Group(Expand_i, {"EmpNum", "WeekEnding", "Job Number", "Section", "Cost Head", "i.1"}, {{"All", each _, type table [EmpNum=nullable text, WeekEnding=nullable date, Job Number=nullable text, Section=nullable text, Cost Head=nullable text, Rate=nullable text, SAT=nullable number, SUN=nullable number, MON=nullable number, TUE=nullable number, WED=nullable number, THU=nullable number, FRI=nullable number, mKey=number, i.1=number, idx=number]}, {"mKey", each List.Sum([mKey]), type number}}),
    Col_i2                    = Table.AddIndexColumn(Group_Rows2, "i2", 1, 1),
    Analysis                  = Table.RemoveColumns(Col_i2,{"All"}),
    Filter_relevant           = Table.SelectRows(Analysis, each ([mKey] < 7)),
    Remove_mKey               = Table.RemoveColumns(Col_i2,{"mKey"}),
    AppendAnalysisToHoursData = Table.Combine({Filter_relevant, Remove_mKey}),
    AddRateTypes              = Table.AddColumn(AppendAnalysisToHoursData, "Rate2", each if [All] <> null then null else 
                                if [mKey] = 1 then {"OT","DT"} else 
                                if [mKey] = 2 then {"ST","DT"} else
                                if [mKey] = 4 then {"ST","OT"} else
                                if [mKey] = 5 then {"OT"} else {"ST"}),
    ExpandRateTypes           = Table.ExpandListColumn(AddRateTypes, "Rate2"),
    Expand_All                = Table.ExpandTableColumn(ExpandRateTypes, "All", {"Rate", "SAT", "SUN", "MON", "TUE", "WED", "THU", "FRI"}, {"Rate", "SAT", "SUN", "MON", "TUE", "WED", "THU", "FRI"}),
    Replace_Rate              = Table.ReplaceValue(Expand_All,each [Rate], each if [Rate] = null then  [Rate2] else [Rate], Replacer.ReplaceValue, {"Rate"}),
    Sort_Table                = Table.Buffer(Table.Sort(Replace_Rate,{{"i2", Order.Ascending}, {"Rate", Order.Descending}})),
    Keep_needed_Cols          = Table.SelectColumns(Sort_Table,{"EmpNum", "WeekEnding", "Job Number", "Section", "Cost Head", "Rate", "SAT", "SUN", "MON", "TUE", "WED", "THU", "FRI"}),
    RestoreTypes              = Value.ReplaceType(Keep_needed_Cols,Value.Type(Change_Type))
in
    RestoreTypes
 
And another…
This is pinarello's file from msg#5 (to use his test data). There's a query called SampleSource which is not required (it's what I used to develop the fnAddMissing function (I change the first 3 lines of the function from:
Code: 
(tbl)=>
let
    Source = tbl,
to:
Code: 
let
    Source = SampleSource,
which means it reverts to a normal query where the steps can be seen and edited)).
My table is the green table next to pinarello's orange table for comparison.
 

Attachments

  • Excelguru11523Fill-in missing time gaps.xlsx
    38.7 KB · Views: 4
Heh - we keep building off of each other's solution :tea:

Originally, I got around the last filter by prefiltering the last group by like this:
Code: 
= Table.Group(Sort_Table, {"i2", "Rate"}, {{"i3", each Table.SelectRows(Table.AddIndexColumn(_, "i3", 1, 1, Int64.Type),  each ([i3] = 1)), Table.Type}})

But I like your new solution by eliminating the last groupby/expansion.

So using your latest I again moved the Add Rate Types before the Append and used a different method than the if/then.
Other than that, it's pretty much the same:
Code: 
let
    Source                    = Excel.CurrentWorkbook(){[Name="tblHours"]}[Content],
    Change_Type               = Table.TransformColumnTypes(Source,{{"EmpNum", Text.Type}, {"WeekEnding", type date}, {"Job Number", type text}, {"Section", type text}, {"Cost Head", type text}, {"Rate", type text}, {"SAT", Int64.Type}, {"SUN", Int64.Type}, {"MON", Int64.Type}, {"TUE", Int64.Type}, {"WED", Int64.Type}, {"THU", Int64.Type}, {"FRI", Int64.Type}}),
    Col_mKey                  = Table.AddColumn(Change_Type, "mKey", each if [Rate] = "ST" then 1 else if [Rate] = "OT" then 2 else 4),
    GroupI                    = Table.Group(Col_mKey, {"EmpNum", "WeekEnding", "Job Number", "Section", "Cost Head", "Rate"}, {{"i", each Table.AddIndexColumn(_, "i", 1, 1), Table.Type}}),
    Col_idx                   = Table.AddIndexColumn(GroupI, "idx", 1, 1),
    Expand_i                  = Table.ExpandTableColumn(Col_idx, "i", {"SAT", "SUN", "MON", "TUE", "WED", "THU", "FRI", "mKey", "i"}, {"SAT", "SUN", "MON", "TUE", "WED", "THU", "FRI", "mKey", "i.1"}),
    Group_Rows2               = Table.Group(Expand_i, {"EmpNum", "WeekEnding", "Job Number", "Section", "Cost Head", "i.1"}, {{"All", each _, type table [EmpNum=nullable text, WeekEnding=nullable date, Job Number=nullable text, Section=nullable text, Cost Head=nullable text, Rate=nullable text, SAT=nullable number, SUN=nullable number, MON=nullable number, TUE=nullable number, WED=nullable number, THU=nullable number, FRI=nullable number, mKey=number, i.1=number, idx=number]}, {"mKey", each List.Sum([mKey]), type number}}),
    Col_i2                    = Table.AddIndexColumn(Group_Rows2, "i2", 1, 1),
    Remove_All                = Table.RemoveColumns(Col_i2,{"All"}),
    Filter_relevant           = Table.SelectRows(Remove_All, each ([mKey] < 7)),
    Analysis                  = Table.AddColumn(Filter_relevant, "Rate2", each Record.FieldOrDefault(
                                                                            [
                                                                            1 = {"OT","DT"},
                                                                            2 = {"ST","DT"},
                                                                            4 = {"ST","OT"},
                                                                            5 = {"OT"}
                                                                            ], Number.ToText([mKey]), {"ST"})),
    HoursData                 = Table.RemoveColumns(Col_i2,{"mKey"}),
    AppendAnalysisToHoursData = Table.Combine({Analysis, HoursData}),
    ExpandRateTypes           = Table.ExpandListColumn(AppendAnalysisToHoursData, "Rate2"),
    Expand_All                = Table.ExpandTableColumn(ExpandRateTypes, "All", {"Rate", "SAT", "SUN", "MON", "TUE", "WED", "THU", "FRI"}, {"Rate", "SAT", "SUN", "MON", "TUE", "WED", "THU", "FRI"}),
    Replace_Rate              = Table.ReplaceValue(Expand_All,each [Rate], each if [Rate] = null then  [Rate2] else [Rate], Replacer.ReplaceValue, {"Rate"}),
    Sort_Table                = Table.Buffer(Table.Sort(Replace_Rate,{{"i2", Order.Ascending}, {"Rate", Order.Descending}})),
    Keep_needed_Cols          = Table.SelectColumns(Sort_Table,{"EmpNum", "WeekEnding", "Job Number", "Section", "Cost Head", "Rate", "SAT", "SUN", "MON", "TUE", "WED", "THU", "FRI"}),
    RestoreTypes              = Value.ReplaceType(Keep_needed_Cols,Value.Type(Change_Type))
in
    RestoreTypes
 
I just noticed that the logic is off when an employee is missing the DT rate. I believe the step should read as:

Code: 
                                                                           [
    Analysis                  = Table.AddColumn(Filter_relevant, "Rate2", each Record.FieldOrDefault(
                                                                            [
                                                                            1 = {"OT","DT"},
                                                                            2 = {"ST","DT"},
                                                                            [U][B]3 = {"DT"}[/B][/U],
                                                                            4 = {"ST","OT"},
                                                                            5 = {"OT"}
                                                                            ], Number.ToText([mKey]), {"ST"})),
 
Last edited:
@p45cal:

I think the solution is very effective. It becomes unbeatable when there are many, instead of only 3 different rate types, one of which is missing from time to time.
 
I have now made the solution of p45cal dynamic.

Based on the different "rates" occurring in the input data, a list is now created, which is then used to generate the missing records. In the process, I was also able to rearrange the query so that no function is anymore required.

Explanation: As long as it is not possible to step through functions, I try to do without functions.
 

Attachments

  • xlguru - Excelguru11523Fill-in missing time gaps - p45cal.xlsx
    61 KB · Views: 2
Last edited:
You must log in or register to reply here.
Back
Top