Help Speeding Up Power Query

[russell80]

I have a query that takes quite a long time to run and I'm hoping there's a faster option out there.

I have 2 tables, one is for employee timesheets that has a record for every timesheet entry for each employee (Date, hours worked, employee etc...). The other is "ORGANISATION" which keeps a record of an employee's manager as it changes over time (Employee, Manager, Date).

What I am trying to do is add the employee's manager to the timesheet table but it has to be who their manager was when the timesheet record was created.

What I've tried to do is add the follwing in power query. The Custom column selects the rows in the ORGANISATION table where the employee in the timesheet record matches the employee in the organisation table and the date of the entry in the ORGANISATION table is less than or equal to the date of the timesheet entry. This gets a table of all the employee's managers prior to and including the date the timesheet entry was created. I then use the table.max funtion to return the latest entry in the ORGANISATION table, thus I get the employee's manager at the time the timesheet entry was created.

I do this multiple times so I can get the hierarchy of managers at the time the timesheet entry was created as well.

This works, but it runs quite slowly. So I'm hoping there's a quicker way I can get the same result!

 #"Buffer Org Table" = Table.Buffer(ORGANISATION),
#"Added Custom" = Table.AddColumn(#"Buffer Timesheets", "Level 0", (T)=> Table.Max(Table.SelectRows(#"Buffer Org Table", (O)=> T[TIME_RES_ID] = O[EMPLOYEE_ID] and O[CREATED] <= T[TIME_DATE]), "EMPLOYEE_ID")),
#"Expanded Level 0" = Table.ExpandRecordColumn(#"Added Custom", "Level 0", {"MANAGER_ID"}, {"Level 0"}),
#"Added Custom1" = Table.AddColumn(#"Expanded Level 0", "Level 1", (T)=> Table.Max(Table.SelectRows(#"Buffer Org Table", (O)=> T[Level 0] = O[EMPLOYEE_ID] and O[CREATED] <= T[TIME_DATE]), "EMPLOYEE_ID")),
#"Expanded Level 1" = Table.ExpandRecordColumn(#"Added Custom1", "Level 1", {"MANAGER_ID"}, {"Level 1"}),
#"Added Custom2" = Table.AddColumn(#"Expanded Level 1", "Level 2", (T)=> Table.Max(Table.SelectRows(#"Buffer Org Table", (O)=> T[Level 1] = O[EMPLOYEE_ID] and O[CREATED] <= T[TIME_DATE]), "EMPLOYEE_ID")),
#"Expanded Level 2" = Table.ExpandRecordColumn(#"Added Custom2", "Level 2", {"MANAGER_ID"}, {"Level 2"}),
#"Added Custom3" = Table.AddColumn(#"Expanded Level 2", "Level 3", (T)=> Table.Max(Table.SelectRows(#"Buffer Org Table", (O)=> T[Level 2] = O[EMPLOYEE_ID] and O[CREATED] <= T[TIME_DATE]), "EMPLOYEE_ID")),
#"Expanded Level 3" = Table.ExpandRecordColumn(#"Added Custom3", "Level 3", {"MANAGER_ID"}, {"Level 3"}),
#"Added Custom4" = Table.AddColumn(#"Expanded Level 3", "Level 4", (T)=> Table.Max(Table.SelectRows(#"Buffer Org Table", (O)=> T[Level 3] = O[EMPLOYEE_ID] and O[CREATED] <= T[TIME_DATE]), "EMPLOYEE_ID")),
#"Expanded Level 4" = Table.ExpandRecordColumn(#"Added Custom4", "Level 4", {"MANAGER_ID"}, {"Level 4"}),
#"Added Custom5" = Table.AddColumn(#"Expanded Level 4", "Level 5", (T)=> Table.Max(Table.SelectRows(#"Buffer Org Table", (O)=> T[Level 4] = O[EMPLOYEE_ID] and O[CREATED] <= T[TIME_DATE]), "EMPLOYEE_ID")),
#"Expanded Level 5" = Table.ExpandRecordColumn(#"Added Custom5", "Level 5", {"MANAGER_ID"}, {"Level 5"}),
#"Added Custom6" = Table.AddColumn(#"Expanded Level 5", "Level 6", (T)=> Table.Max(Table.SelectRows(#"Buffer Org Table", (O)=> T[Level 5] = O[EMPLOYEE_ID] and O[CREATED] <= T[TIME_DATE]), "EMPLOYEE_ID")),
#"Expanded Level 6" = Table.ExpandRecordColumn(#"Added Custom6", "Level 6", {"MANAGER_ID"}, {"Level 6"}),
#"Added Custom7" = Table.AddColumn(#"Expanded Level 6", "Level 7", (T)=> Table.Max(Table.SelectRows(#"Buffer Org Table", (O)=> T[Level 6] = O[EMPLOYEE_ID] and O[CREATED] <= T[TIME_DATE]), "EMPLOYEE_ID")),
#"Expanded Level 7" = Table.ExpandRecordColumn(#"Added Custom7", "Level 7", {"MANAGER_ID"}, {"Level 7"}),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Expanded Level 7", {"Level 7", "Level 6", "Level 5", "Level 4", "Level 3", "Level 2", "Level 1", "Level 0"}, "Attribute", "Value")

[BA_Pete · Accepted solution]

Hi @russell80 ,

So, I think you have a couple of options:

1) If you really want to do this in PQ, then you could 'explode' your SCD table i.e. expand it so it has a row for every date for every employee/manager combination.

You would add a custom column to your SCD table something like this:

List.Transform(
    {Number.From(mgrStartDate)..Number.From(mgrEndDate)},
    each Date.From(_)
)

From here, you can merge/relate on an employee-date merged column to get the correct value at any given date.

Pros:

- Stays in PQ so can be offloaded to a gateway

- Relatively simple to implement and understand

Cons:

- Can result in an absolutely huge table

2) Use DAX calculated columns to bring in your SCD values against the Timesheet table.

You would add columns something like this:

empMgr = 
CALCULATE(
	VAR __empCodeRow = VALUES(Timesheet[employeeCode])
	VAR __dtHoursRow = VALUES(Timesheet[dtHours])
	RETURN
	MAXX(
		FILTER(
			scdOrganisation,
			scdOrganisation[employeeCode] = __empCodeRow
			&& scdOrganisation[mgrStartDate] <= __dtHoursRow
			&& scdOrganisation[mgrEndDate] >= __dtHoursRow
		),
		scdOrganisation[mgrName]
	)
)

Pros:

- Doesn't increase refresh time

- Relatively simple to implement and understand

Cons:

- Offloads the work to enduser memory at runtime - may impact visual performance

Pete