How to differentiate between different rows in different columns

[TH20020520]

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 as

employee id - start date- end date - position 

123.                1-1-2025    1-20-2025.   321

123.                21-1-2025.   1-2-2025.   321

123.                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.

[Akash_Varuna · Accepted solution]

Hi @TH20020520 Could you try these please 

1. Sort the Table:
   In Power Query, sort by Employee ID, Position, and Start Date.

2. Add Index Column:
   Add an Index column to track row order.

3. 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 NewGroup

4. Group Rows:
   Generate unique group indices for rows meeting the criteria.
   If this post helped please do give a kudos and accept this as a solution
   Thanks In Advance

[v-sgandrathi]

Hi @TH20020520,

Thankyou @dufoq3 and @Akash_Varuna  I appreciate your prompt response to the issue.

 I'm glad that your query was resolved. If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

 Thank you for your understanding!

[v-sgandrathi]

Hi @TH20020520,

I wanted to follow up on our previous suggestions regarding the issue. We would love to hear back from you to ensure we can assist you further.

If my response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.

Thanks for connecting with the Microsoft Fabric Community Forum.

[v-sgandrathi]

Hi @TH20020520,

 I'm glad that your query was resolved. If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Thank you for your understanding!

[dufoq3 · Accepted solution]

Hi @TH20020520, be more pricise for next time please:

1. once the datetime is in "d-m-yyyy" format and once in "m-d-yyyy"
2. the date 30th of Feb doesn't exist
3. you should provide sample data as a table (if you don't know how - read note below my post please)

I'm not sure what should be your output - you should provide expected output based on sample data, but I gues you want something like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dcq5DQAgEAPBXhwDwubtBV3/bUDEkZCNtLsWqJIQwMiorHao7C4iLNxLz3bwuTg8aX43eWF319ZhtgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"employee id" = _t, #"start date" = _t, #"end date" = _t, position = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"start date", type date}, {"end date", type date}, {"position", Int64.Type}}, "sk-SK"),
    L = List.Buffer(Table.ToRows(ChangedType)),
    Gen = List.Skip(List.Generate(
        ()=> [ x = -1, y = 0 ],
        each [x] < List.Count(L),
        each [ x = [x]+1, r = [r]+1, y = try (if {L{x}{0}, L{x}{3}} = {L{[x]}{0}, L{[x]}{3}} and Duration.Days(L{x}{1} - L{[x]}{2}) <= 90 then [y] else r) otherwise [y]+1 ],
        each [y])),
    Merged = Table.FromColumns(Table.ToColumns(ChangedType) & {Gen}, Value.Type(Table.FirstN(ChangedType, 0) & #table(type table[Index=Int64.Type], {})))
in
    Merged

[Akash_Varuna · Accepted solution]

Hi @TH20020520 Could you try these please 

1. Sort the Table:
   In Power Query, sort by Employee ID, Position, and Start Date.

2. Add Index Column:
   Add an Index column to track row order.

3. 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 NewGroup

4. Group Rows:
   Generate unique group indices for rows meeting the criteria.
   If this post helped please do give a kudos and accept this as a solution
   Thanks In Advance