Forum Discussion
Solved
Identify service episodes and group dates
Hi All, I would like to thank all of you who have helped me in the past and i am looking for additional help. I am trying to identify episodes of service and identify the earliest start date ...
Hi hpatel24779
Sorry for getting back to you later than expected. I have attached the revised .PBIX file and snapshots for reference. Please review it and let us know if you need any additional help.
Regards,
Microsoft Fabric Community Support Team.
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 jgeddes
I need both start and end dates for all rows.
I will keep playing around and see where I get but appreciate your help.
Kind regards
Hetal
Apologies, I misread the requirements.
Does this work for you?let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZBRCsQwCETvku+CdWLS7Wd7jdL7X2PHJC7NUogg+nBmcl3pSEtSFgQrn27s0730Bf4WWbKyx/ojMqtIdWB/BYy19xPIz9vFhVW08U6ZwLwvDTmHL5PNj9dnG3u3p7wcwKx+DnsmY+hOtcyA9WkNggqfmWg+x1QjgC+6IhDJIGo9SxD+ZcoxImIYZMT7Cw==", 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}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"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.AddIndexColumn(_, "Index", 0,1,Int64.Type), type table}}), Custom2 = Table.TransformColumns(Custom1, {{"AllRows", each Table.AddColumn(_, "EndCont", (r)=> try if Number.From(r[EndDate] - [StartDate]{r[Index]+1}) < -1 then r[Index] else null otherwise r[Index], Int64.Type), type table}}), Custom3 = Table.TransformColumns(Custom2, {{"AllRows", each Table.AddColumn(Table.FillUp(_, {"EndCont"}), "EndIndex", (r)=> if r[EndCont] < List.PositionOf([EndDate], null, Occurrence.First) then r[EndCont] else List.PositionOf([EndDate], null, Occurrence.First)), type table}}), Custom5 = Table.TransformColumns(Custom3, {{"AllRows", each Table.AddColumn(_, "NewStartDate", (r)=> [StartDate]{List.PositionOf([EndIndex], r[EndIndex], Occurrence.First)}, type date), type table}}), Custom4 = Table.TransformColumns(Custom5, {{"AllRows", each Table.AddColumn(_, "NewEndDate", (r)=> [EndDate]{r[EndIndex]}, type date), type table}}), Custom6 = Table.TransformColumns(Custom4, {{"AllRows", each Table.RemoveColumns(_, {"Index", "EndCont", "EndIndex"}), type table}}), #"Expanded AllRows" = Table.ExpandTableColumn(Custom6, "AllRows", {"Line", "StartDate", "EndDate", "NewStartDate", "NewEndDate"}, {"Line", "StartDate", "EndDate", "NewStartDate", "NewEndDate"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded AllRows",{{"Line", Int64.Type}, {"StartDate", type date}, {"EndDate", type date}, {"NewStartDate", type date}, {"NewEndDate", type date}}) in #"Changed Type1"Hi jgeddes
From the screen shot provided, i think this is what i am expecting.
I will further update you when i have applied to code to my data.
Thanks
Hetal
hi jgeddes
Are you able to provide the example pbix. I am trying to amend the code you have given and seem to be going wrong somewhere.
Would appreciate if you can send the .pbix file and i can go through and see where i am going wrong.
kind regards
Hetal
Here you go. Hope it works for you.