identifying continuous dates per person in new column

Hi hpatel24779 

Thanks for the solutions p45cal  and lbendlin offered, and i want to offer some more information for user to refer to.

hello hpatel24779 , basd on the data you hace offered, as lbendlin mentioned, the time difference for client4 is 15, so i change the date for client4 from 4/9/2024 to 3/9/2024, then you can refer to the following code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZBLDoMwDESvUmUdyV/SsCw9BuL+16hpEicBsWBh82Y8k30PFGL42McImICRkg1CgGIDczhiZ9DWWtYx0BtwPQeZGMq+NtO1+ujECPo6hpf/2q7ygRsZT9fl3K4rELqcJjlfW3T5SX/rdb7JZWJc/i+YgejOWO+2NtMESD2GthhSHrz4WNPcfUZmfaijz3WWFkP9snGLcyNjL1ZiVPnxAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Client ID" = _t, Service = _t, #"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client ID", Int64.Type}, {"Service", type text}, {"Start Date", type text}, {"End Date", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Start Date", type date}, {"End Date", type date}}, "en-GB"),
    #"Grouped Rows" = Table.Group(#"Changed Type with Locale", {"Client ID", "Service"}, {{"Count", each Table.AddIndexColumn(_,"Index",1,1),type table}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Start Date", "End Date", "Index"}, {"Start Date", "End Date", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Count", "Custom", each let a=[Index],
b=[Service],
c=[Client ID],
d=try Table.SelectRows(#"Expanded Count", each [Index]=a+1 and [Service]=b and [Client ID]=c)[Start Date]{0} otherwise null
in Duration.Days(d-[End Date])),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down", "Custom.1", each let _client=[Client ID],
_service=[Service],
_table=Table.Buffer(Table.Sort(Table.SelectRows(#"Filled Down",each [Client ID]=_client and [Service]=_service),{"Index",Order.Ascending})),
_counttable=Table.RowCount(_table),
_count2=Table.RowCount(Table.SelectRows(_table,each [Custom]=1 or [Custom]=14))
in _counttable=_count2),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Required Start", each let _client=[Client ID],
_service=[Service],
_table=Table.Buffer(Table.Sort(Table.SelectRows(#"Filled Down",each [Client ID]=_client and [Service]=_service),{"Index",Order.Ascending}))
in if [Custom.1]=true then List.First(_table[Start Date]) else [Start Date]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Required End", each let _client=[Client ID],
_service=[Service],
_table=Table.Buffer(Table.Sort(Table.SelectRows(#"Filled Down",each [Client ID]=_client and [Service]=_service),{"Index",Order.Ascending}))
in if [Custom.1]=true then List.Last(_table[End Date]) else [End Date]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Index", "Custom", "Custom.1"})
in
    #"Removed Columns"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.