Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
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
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.