Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I have 3 tables based on which I need a combined output.
The three tables are:
1. DateTable
2. EmployeeData
3. TimeSheet
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:
The result of my sample data would be the following:
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).
Solved! Go to 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
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |