how to group consecutive date/time range with start and end timing

[Anonymous]

Hi all, 

Tried to find existing solutions but cant seem to find a one that solves my problem. 

I am trying to transform my data in power query of Power BI. 

I have a data table like the below and would like to transform it to one that groups consec same status and date time to reflect start and end time of each status instead. 

Thanks for the help in advance!

Desired Format

Vehicle Number	Start Time	End Time	Status Mode
Vehicle A	14/3/2023 0900hr	14/3/2023 0924hr	A
Vehicle A	14/3/2023 0924hr	14/3/2023 1010hr	B
Vehicle B	14/3/2023 0856hr	14/3/2023 0958hr	A
Vehicle B	14/3/2023 0958hr	14/3/2023 1111hr	B

My Data Currently

DateTime	Vehicle Number	Status Mode
14/3/2023 0900hr	Vehicle A	Status A
14/3/2023 0910hr	Vehicle A	Status A
14/3/2023 0924hr	Vehicle A	Status B
14/3/2023 0928hr	Vehicle A	Status B
14/3/2023 1010hr	Vehicle A	Status A
14/3/2023 0856hr	Vehicle B	Status A
14/3/2023 0914hr	Vehicle B	Status A
14/3/2023 0958hr	Vehicle B	Status B
14/3/2023 1058hr	Vehicle B	Status B
14/3/2023 1111hr	Vehicle B	Status A

[Jakinta]

This should do the trick.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTRN9Y3MjAyVjCwNDDIKFLSUQpLzchMzklVcASyHZVidVAVGRKjyMgEQ5ETpiILgooMDYixzsLUDEWRE3aHmxChyNQCQxGmm4hRBATYrIsFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateTime = _t, #"Vehicle Number" = _t, #"Status Mode" = _t]),
    Group1 = Table.Group(Source, {"Vehicle Number", "Status Mode"}, {{"Start Time", each _[DateTime]{0}, type table }}, GroupKind.Local),
    Group2 = Table.Combine ( Table.Group(Group1, {"Vehicle Number"}, {{"A", each let t=_ in Table.RemoveColumns ( Table.RemoveLastN ( Table.AddColumn ( Table.AddIndexColumn(t,"i",1), "End Time", each t[Start Time]{[i]}), 1), {"i"}), type table }}) [A] )
in
    Group2

[Anonymous]

hi @Jakinta ,thanks much for your response. 

Unfortunately, i tried out the code but i dont get the grouping that i require. The vehicle and status code are still ungrouped and the start and end timing are all just 5 seconds apart. Im not sure what is wrong.