Forum Discussion
Identify service episodes and group dates
- 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.
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"- hpatel247791 year agoHelper II
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
- hpatel247791 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.