Forum Discussion
hpatel24779
Helper II
1 year agoIdentify 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 ...
- 1 year ago
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.
jgeddes
Super User
1 year agoApologies, 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"hpatel24779
Helper II
1 year agohi 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
- jgeddes1 year ago
Super User
Here you go. Hope it works for you.