Calculation for consecutive work days for employee

[babyjb1979]

Hello everyone,

I've been tasked with creating a report that tracks consecutive workdays. Within the employee transaction table, there are two key columns: [Employee ID] and [Work Date]. I've already added a new column that's working well, except it's not applied correctly when employees are grouped together.

In an attempt to refine the report further, I introduced another column. Unfortunately, this didn't yield the desired results.

I'm in need of your assistance to make this report accurate and effective. Your help would be greatly appreciated!

[lbendlin]

This can be done much simpler in Power Query.

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[babyjb1979]

Please advise the solution by Power Query. I am in a hurry! Thank you a million!!!!

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc2xDcAgDETRXVwjnTmDSWZB7L8GRarI177i/70tmLmsmQd6B8NO+yGnwqXwUfgKDFdY7wNemwNkwQlPgfxG5wI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Number" = _t, #"Work Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee Number", Int64.Type}, {"Work Date", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Consecutive", each if [Index]=0 then 1 else List.Accumulate({1..[Index]},1,(state,current)=> if #"Added Index"[Work Date]{current-1}=#"Added Index"[Work Date]{current}+#duration(-1,0,0,0) then state + 1 else 1))
in
    #"Added Custom"

[babyjb1979]

But once I add the new column in Power Query, when close and apply, it is loading forever. There are 1.3 million rows. It will load many days. How can I optimize the query? 

Thank you!

[lbendlin]

Please provide sample data that fully covers your issue.

[babyjb1979]

Please revise employee number to different numbers.  Thank you!

[babyjb1979]

Employee Number	Work Date
32667	03/11/23
32667	03/25/23
32667	03/27/23
32667	03/28/23
32667	03/29/23
32667	03/30/23
32667	03/31/23
32667	04/08/23
32667	04/22/23
32667	05/06/23
32667	05/20/23

[babyjb1979]

Please revise employee number to different numbers.  Thank you!