Forum Discussion
hpatel24779
1 year agoHelper II
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 ...
- 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
1 year agoSuper User
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"hpatel24779
1 year agoHelper II
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
- jgeddes1 year agoSuper User
Here you go. Hope it works for you.