none
Way to Select a Subset of a Set and only output that data? RRS feed

  • Question

  • OK, background on the data...I'm trying to build a dynamic query that receives data from timesheet entries...the query itself will then take the data and automatically calculate regular time and overtime based on the time durations.  Everything works for the most part, but I'm having one final issue that is hanging me up right now...

    The data comes in as a table with these headers:

    Full Name | Date | Duration of Work | Other non-relevant data (for this question)

    Thanks to Chris Webb, I was able to write this function to create a column that calculates a running tally by week for each employee:

    let
        WTD = (HoursTable, EndDate,FullName) =>
        let
           FilteredTable=Table.SelectRows(HoursTable, each ([Date]<=EndDate and Date.WeekOfYear([Date])=Date.WeekOfYear(EndDate)) and [Full Name]=FullName),
           HoursColumn = Table.Column(FilteredTable, "Payable Time"),
           WTDHours = List.Sum(HoursColumn)
        in
          WTDHours
    in
        WTD

    The output for this is a running tally of weekly hours for each employee called WTDHours.  For the employees that have 1 shift per day, this works great: the WTDHours outputs the correct summed number on each relevant row.  

    The trouble happens when an employee has more than one shift on the same day - in that situation, I end up with duplicate "full day" hours in the WTDHours column.

    I'm looking for any recommendations to help me...my thoughts are that I need to filter the function so it still adds up the total daily hours, but it only outputs the daily total on the "last" time/entry of the shift...so if the employee works 7am-9am, 10am-2pm, and 3pm-6pm, the daily running tally in WTDHours only shows up on the 3pm-6pm row, but thus far I'm unable to do that.

    Any good ideas out there?  Pictures of the output attached for reference...

    Thursday, April 2, 2015 5:46 AM

Answers