cancel
Showing results for
Did you mean:
Regular Visitor

## Combined table with SUMIF criteria from another table

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.

*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).

1 ACCEPTED SOLUTION
Microsoft

@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}}),
#"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]),
in

Regards.
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Microsoft

@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}}),
#"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]),
in

Regards.
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!