Identify service episodes and group dates

Hi,

Below is a link to file exported in Excel so hope this works

Service Episode File 

kind regards

Hetal

Here is one way this might work.
Starting with example data like this...

You can get the following result... 

With this code...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY9LDsMwCETvwtoS4eOkWdbXiHL/a3SIoUqqSkZC8DwzHAe9qZGglHXBkw09nW0u9GdhbIJely9hqM5rAPtfwFH7lFC7a/cwFpaLD8pZPfp+ISNzOW8hvt7b2kc8gXIBT/eR8Zxz2Aj/X08gjCWnUvFGxjNWrdzK4jNpEeEoGGsdUPY44PwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Client = _t, Line = _t, StartDate = _t, EndDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client", type text}, {"Line", Int64.Type}, {"StartDate", type date}, {"EndDate", type date}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,#date(9999, 12, 31),Replacer.ReplaceValue,{"EndDate"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"Client"}, {{"AllRows", each _, type table [Client=nullable text, Line=nullable number, StartDate=nullable date, EndDate=nullable date]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {{"AllRows", each Table.Sort(Table.Unpivot(_, {"StartDate", "EndDate"}, "Attribute", "Value"), {"Value", "Line"}), type table}}),
    #"Added Custom2" = Table.AddColumn(Custom1, "Custom", each let ListA = Table.SelectRows([AllRows], each [Attribute] = "EndDate")[Line], ListB = List.Numbers(1,List.Count(ListA), 1) in List.Transform(List.Numbers(0,List.Count(ListA),1), each if ListA{_} = ListB{_} then ListA{_} else null)),
    Custom2 = Table.TransformColumns(#"Added Custom2", {{"AllRows", each Table.Pivot(_, List.Distinct([Attribute]), "Attribute", "Value"), type table}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(Custom2, "AllRows", {"Line", "StartDate", "EndDate"}, {"Line", "StartDate", "EndDate"}),
    #"Added Custom" = Table.AddColumn(#"Expanded AllRows", "newEndDate", each if [Custom]{[Line]-1} = [Line] then [EndDate] else null, type date),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Custom", "EndDate"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"StartDate", type date}, {"Line", Int64.Type}})
in
    #"Changed Type1"

Hopefully this gets you pointed in the right direction. 

Hi,

Does this link work?

https://1drv.ms/x/c/9eb0597c98b072c8/ETotvxO5OGdOnbOX3yiidqoBWUuEZzv-vRJmAg33xSBxVQ?e=xh1Fii

Kind regards 

Hetal

Hi hpatel24779 

Thank you for submitting your question to the Microsoft Fabric Community Forum, and thanks to ronrsnfld and jgeddes  for offering helpful suggestions.

Could you let us know if the suggested solution resolved your issue? This information can assist other community members facing similar challenges.

Thank you.

Hi ronrsnfld 

Thanks for the follow up. I understand your concern. The intent of my follow-up was not to "nag," but to check whether the proposed solutions actually helped the OP. Confirming the resolution can benefit both the OP and future community members who may face the same issue, as it helps ensuring the thread remains complete and informative.

Regards,
Karpurapu D.

Hi hpatel24779 

Thank you for reaching out to the Microsoft Fabric Community Forum.

I reproduced the scenario on my end using sample data and it worked successfully. To help you better understand the implementation, I’ve attached the .pbix file and screenshot for your reference. Please take a look at it and let me know your observations.

Thank you for being part of the Microsoft Fabric Community!

Regards,
Microsoft Fabric Community Support.

Hi hpatel24779 

I wanted to check if you’ve had a chance to review the information provided. If you have any further questions, please let us know. Has your issue been resolved? If not, please share more details so we can assist you further.

Thank You.

Hi hpatel24779 

Just checking in as we haven't received a response to our previous message. Were you able to review the information above? Let us know if you have any additional questions.

Thank You.