Creating derived columns via Power Query

[Rohitb]

Hello,

Below is my Source Table:

Employee Id	DOJ	DOL	IsContinuityNeeded	ContinuedFor
1	7/28/2022	9/25/2023	1
2	9/23/2023	11/30/2023	1
3	10/2/2023
4	5/30/2023	9/15/2023	1
5	2/25/2023	9/10/2023
6	12/15/2023			4
7	11/5/2023			2
8	12/6/2023

I need to create 2 derived columns during data transformations (Power Query).

Below is the final Ouput State required.

Employee Id	DOJ	DOL	IsContinuityNeeded	ContinuedFor	DOL_Updated	DOJ_Updated
1	7/28/2022	9/25/2023	1		UTCNOW()	7/28/2022
2	9/23/2023	11/30/2023	1		11/5/2023	9/23/2023
3	10/2/2023					10/2/2023
4	5/30/2023	9/15/2023	1		11/30/2023	5/30/2023
5	2/25/2023	9/10/2023			9/10/2023	2/25/2023
6	12/15/2023			4		12/15/2023
7	11/5/2023			2		12/01/2023
8	12/6/2023					12/6/2023

Here the requriment is :

Employees with No Departure Date (DOL):
For employees without a departure date (DOL),  the DOJ_Updated should be their actual joining date. (Example EmployeeID : 3 & 8 )

Employees with Departure Dates and No Continuity Requirement (IsContinuityNeeded is blank):
For employees with departure dates (DOL) and no continuity requirement (IsContinuityNeeded is blank), their DOJ_Updated & DOL_Updated should be same as the DOJ & DOL respecively. (Example EmployeeID : 5)

Employees with Departure Dates and Continuity Requirement (IsContinuityNeeded is 1):
a. If there's no replacement employee (ContinuedFor column is blank), the DOL_Updated should be the max data/utcnow/today.

DOJ_Updated should be same as DOJ. (Example EmployeeID : 1 )

b. If a replacement employee exists, and they joined before the departing employee (DOJ of replacement < DOL of departed),
the DOJ_Updated of the replacement employee should be the date after DOL Month end date of the departing employee.
For, the departing employee, DOJ_Updated & DOL_Updated remains same as DOJ & DOL respectively. 
(Example EmployeeID : 2/7 )

c. If a replacement employee exists, and they joined after the departing employee left (DOJ of replacement > DOL of departed), the DOJ_Updated for the replacement employee is their actual DOJ where as for the departing employee DOL_Updated is last day of the previous month of the DOJ of the replacement employee . (Example EmployeeID : 6/4 )

How can we acheive this ?

@amitchandak@lbendlin 

Thanks in advance.

[NandanHegde]

Not sure whether the below approach is a good one but you can try as below :

Create a duplicate table of the employee table.
Then below custom columns leveraging PositionOf and getting the value for that index :

DOJ_Updated :

if [ContinuedFor] = " " then [DOJ] else try Elookup[DOL]{ List.PositionOf( Elookup[EmloyeeId] , [ContinuedFor] ) } otherwise ""

DOL_Updated :
if [IsContinuityNeeded] <> 1 then [DOL] else if (try Elookup[DOJ]{ List.PositionOf( Elookup[ContinuedFor] , [EmloyeeId] ) } otherwise "") <>"" then (try Elookup[DOJ]{ List.PositionOf( Elookup[ContinuedFor] , [EmloyeeId] ) } otherwise "") else Date.EndOfMonth(DateTime.LocalNow())

@lbendlin :

Not sure whether this is good as duplicate table is being created. I am not aware of usage of PositionOf within the same tagble due to circular table dependency

[lbendlin]

Where did ELookup  come from?

[NandanHegde]

Hey,

The ELookup is the name of the duplicate table of Employee

[lbendlin]

I already showed you a solution in your prior thread.

[Rohitb]

Hello @lbendlin 
Thankyou for your reply.

I was able to acheive the required output via DAX using the below query :

DOJ_Updated =
IF(Input[ContinuedFor]=BLANK(),
Input[DOJ],
LOOKUPVALUE(Input[DOL],Input[ID],Input[ContinuedFor])
)
DOL_Updated =
IF(Input[IsContinued]<>1,
Input[DOL],
IF(LOOKUPVALUE(Input[DOJ],Input[ContinuedFor],Input[ID])<>BLANK() ,LOOKUPVALUE(Input[DOJ],Input[ContinuedFor],Input[ID]),EOMONTH(UTCTODAY(),0) //LOOKUPVALUE(Input[DOJ],Input[ContinuedFor],Input[ID])
))

As you had suggested that this should not be done via DAX, so I was trying via fabric dataflow gen2 for my learning & using Power Query to get the desired result, however I was unable to find an alternative to LOOKUPVALUE in Power Query.

[lbendlin]

There are many ways to have the equivalent of LookupValue in Power Query.  What have you tried and where are you stuck?