I have table that contains for examples Employee Id- Start date of assignment- End date of assignment- position Id and there is possibility that the same employee can be at the same position for different periods of time as it's has been considered as different assignment such asemployee id - start date- end date - position 123. 1-1-2025 1-20-2025. 321123. 21-1-2025. 1-2-2025. 321123. 17-2-2025. 30-2-2025 321 123. 2-2-2025. 16-2-2025. 456 so I want to calculate by using index, if the current employee Id& Position are equal to previous employee id & positions, And the difference between end date of previous row and start date of the current <=90 days so I need the index No to be the same for these rows where employee working in the same position but for a different assignment and the period time between these assignments are less that 90 days, else ? This specific column will have the unique index No no repeated.

Hi TH20020520 Could you try these please Sort the Table:In Power Query, sort by Employee ID, Position, and Start Date.Add Index Column:Add an Index column to track row order.Add Custom Column:if [Employee Id] = Table.PreviousRow("Employee Id") and [Position] = Table.PreviousRow("Position") and Duration.Days(Date.From([Start Date]) - Date.From(Table.PreviousRow("End Date"))) <= 90 then PreviousGroup else NewGroupGroup Rows:Generate unique group indices for rows meeting the criteria.If this post helped please do give a kudos and accept this as a solutionThanks In Advance

How to differentiate between different rows in different columns

Forum Discussion

Related content

Different schema into different destination

Page Level filter for different groups

Difference between different Staging lakehouses

1. How to add two different calculated columns of two different table

Dynamic Calculation Different Period