Combined table with SUMIF criteria from another table

[sjijmon]

Hi everyone,

I have 3 tables based on which I need a combined output.

The three tables are:

1. DateTable

• Date (e.g. 1-Jan-2018)
• Workday (1 or 0)
• HoursRequired (8.5 or 0)

2. EmployeeData

• Employee (e.g. 'John')
• Entry Date (e.g. 5-Jan-2018)
• Exit Dat (e.g. 15-Jan-2018)

3. TimeSheet

• Date (e.g. 5-Jan-2018)
• Employee (e.g. 'John')
• HoursWorked (e.g. 9)

With these 3 tables combined I need to generate an output table (interactive and linked to input tables):

Rows: Date

Colums: Employees

Values: [HoursRequired] - [HoursWorked]

The tricky part for me is that this output needs to combine information from 3 different tables:

• Combination of DateTable & EmployeeData to find out whether an employee is ACTIVE*
• Combination of DateTable & EmployeeData to find out the required hours to be worked for any given time frame (by multiplying the required amount of hours to be worked on a given day with the number of ACTIVE* employees) -> [HoursRequired]
• Combination of DateTable & Timesheet to find out how much a particular employee worked on a particular date -> [HoursWorked]
• Combination the last two points ([HoursRequired]- [HoursWorked]) to give the final value in which I am interested in, which can be interpreted as the overtime of the employees.

The result of my sample data would be the following:

• Overtime John: -38.5h
• Overtime Sandra: -67.0h

To better illustrate my request, please find a power bi datasample (link) with an illustrative picture of the output table I am looking for.

Many thanks in advance for your help.

*An employee is ACTIVE between his/her entry and exit date (e.g. John is ACTIVE from 5-Jan and to 15-Jan, and thus required to work 8.5h at any workday within that time frame).

[v-yuezhe-msft · Accepted solution]

@sjijmon,

Add blank queries in your PBIX file and paste the following code to test.

EmployeeData

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUTLUN9U3MjC0ADMNoexYnWil4MS8lKJEiLARQokRTEksAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Employee = _t, #"Entry Date" = _t, #"Exit Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Entry Date", type date}, {"Exit Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Dates([Entry Date],Duration.Days(Duration.From([Exit Date]-[Entry Date]))+1,#duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

TimeSheet

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31TcyMLRQ0lHyys/IA1KWSrE6IHEzNHFDHOJGUHFLIsWNcYibQMWNjGESwYl5KUWJQIY5TMoEQ8oQ6KxYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Employee = _t, HoursWorked = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Employee", type text}, {"HoursWorked", Int64.Type}})
in
    #"Changed Type"

DateTable

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddG7DcAgFEPRXahRwOabWSL2XyM/pYpd0JwCvSsfR0BCYsYMMeB6c2thxZupuWiumpvm/nF+3otD4dQf7JqRjZtGmEgUdQuqVNOIbnwYN6kwrczqGkKq6aRZk2ZOmlaaVspNOaWazmI2Lf9N1wk=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Workday = _t, HoursRequired = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Workday", Int64.Type}, {"HoursRequired", type number}})
in
    #"Changed Type"

Merge1

let
    Source = Table.NestedJoin(EmployeeData,{"Employee", "Custom"},TimeSheet,{"Employee", "Date"},"TimeSheet",JoinKind.LeftOuter),
    #"Expanded TimeSheet" = Table.ExpandTableColumn(Source, "TimeSheet", {"HoursWorked"}, {"TimeSheet.HoursWorked"}),
    #"Grouped Rows" = Table.Group(#"Expanded TimeSheet", {"Employee", "Custom"}, {{"hourwork", each List.Sum([TimeSheet.HoursWorked]), type number}})
in
    #"Grouped Rows"

Merge2

let
    Source = Table.NestedJoin(Merge1,{"Custom"},DateTable,{"Date"},"DateTable",JoinKind.LeftOuter),
    #"Expanded DateTable" = Table.ExpandTableColumn(Source, "DateTable", {"HoursRequired"}, {"DateTable.HoursRequired"}),
    #"Added Custom" = Table.AddColumn(#"Expanded DateTable", "Workedhour", each if [hourwork] is null then 0 else [hourwork]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "time", each [Workedhour]-[DateTable.HoursRequired])
in
    #"Added Custom1"

Regards.
Lydia