time/hours duration

[topazz11]

Hello,

I want to try create the duration between key in and key out data.

What I tried was to convert them in power M but not working.  

This is the error I got.  I tried to pivot the type column, but it doesn't give me right dataset.

is there a better way to achieve this with measure or M query?

Desired Output

pbix 

Can anyone help me on this.

Thanks in advance!

[Anonymous · Accepted solution]

Hi @topazz11 ,

Please try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lVe7bhtBDPyVQLV9IPd92zlpkjhAighIYbhIIcAGHBtw4iJ/nyFPMvZOuttdFVIhDIaP4ZB7d7e7Pfz78OV5d7UzZOw1jdfEe7aZQvZxIG8JH/z77XDA98cUd/dXE+j7298FirIJmdJANq6iCqp0bcKeQ3aUvR14XAeVVBPKZ47Z2MEQtVFZzcoBZwZyJ9DPX09P+Nkzr3ABRmO2nD3SinYdNs9rlAh9zD6AbAO1SExhBjF6wGIrWZLM/JgJKN+cmcBMNkhu3IbNy0h7dhklkTL6U4ifHl4f/8jvSGt1JKkjyk+xqMgl3JlAOJPJFq3mdtgobB6wVBTy9vE3vm94VVdApWwYRSlirKKCcBmtyBRhW4h+LwpGcjQwtxdScNAIGu4H67ZwZ3R0pKPURUcREsmeC/3X6axk5x0MAUU5wT6/vb48b9Mpzvrs7EAjbeDO6Jz0wI09wy0wm40Eug1bkolMEGbsGm4rlYSabShstUpmJES0TsjebfVUkB9sVmCs9YexUgFraDerIcP+TVe7Wb2EpsnZmoFzOhTEqro2w1zSoQOE1pUmVF0brA1Abq7oW2Vt8CgFsYQlVbhkXciCc1JIEXLcGoA53eTlHoxFZg10SQsJw8MO2Jy3OV3UKBOMucvNBYeVGDKXWq72jYOoEmJ2pZhrBisoh6nJzm2a+ZzKiyIZc5oq+2ZBpj6JfS/9js2KZCcxSt9S17wJTmSsmkztgwOcLJ0kfCa0h2n0ciI9FXrCVBfCnM5aUKc7uYkcT5syWdCxhIlhJVuouU5Huhohr1Bk9/XloTI7JGLGEIibvLNdgM3ISI0B966hSknmbKTGINsjFYZSzY30NrG4GFxX5wQHLoem99xdlGQMxCvteU3WdxxgEqXXovi4DpuTRbEFFyZjbh8CwQU5fH0/DhPH6g12/c6bB+nV8mCVqaLJBZnXB4TVp0Bob4ATLeNlZGojsKBzx0vIYKO2GwPpAeXlGdE1qYIz4s26+LvoOAuju7BVy6vmnE5labnoePUaIrU9EYrvE4o5ziqepx1rDjB56zh9Wb3T1c4TQcGDwOfbz5PprW6OBWm2PNK7y02Hb7vl8fENMfOSFjJJbBrTi7Z8/x8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"type" = _t, date_time = _t, name = _t, car = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"type", type text}, {"date_time", type datetime}, {"name", type text}, {"car", type text}}),
    #"Inserted Date" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([date_time]), type date),
    #"Grouped Rows" = Table.Group(#"Inserted Date", {"name", "car", "Date"}, {
        {"Key Out", each List.Max(Table.SelectRows(_, each [type] = "Key Out")[date_time]), type datetime},
        {"Key In", each List.Min(Table.SelectRows(_, each [type] = "Key In")[date_time]), type datetime}
    }),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Duration", each Duration.ToText([Key In]-[Key Out]))
in
    #"Added Custom"

output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[topazz11]

@Anonymous 

I appreciate your help. 

the data has multiple records on the same date.

your query picks up the max date only so 1 per each date... how can it shows all data per each date?

 ex, John should show 3 different rows on 8/1/2023

[Anonymous]

What have you tried already?