Forum Discussion
tracyhopaulson
2 years agoResolver I
Calculate Time
How do I calculate total time between start time and stop time when my data is like this?
- 2 years ago
Once you examined the code, replace the Source step with your own source.
tracyhopaulson
2 years agoResolver I
hope this works for you https://drive.google.com/file/d/1qTDip455X-M_SefingPvcL7gOaqr__4g/view?usp=drive_link
lbendlin
2 years agoSuper User
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZZdT8IwFIb/SsO1if0a7XqnXGmCYvDOeDFHBeLYTLeR+O8tiNKVdh5lhIvmNHmf09Nz9vbpaYRm2tRVia5GFyNyKS8pptxZorEiqcIYXU2jUfuf5yu9aAu9sOtH0+ZvaJqty0aXWZnr0fPF7xyqMN0pzg6K4Y2/ctIj52uJhMLMOU8outfOtrpYl0u7vjcLbRC2PzmWFASRinbK42V+p19MVr9l6LZ6AeoxPKwel57eTYlmploaXdf/O3OqGPc0z6wioYcyzo6Sk2rzXuhmf+z/SfK022bBHbe6R8xYMBLAEHLkHNZ2QBh12iwYjnKkEAmQQ7Dinc6NbUXvl0sWYlGHRb+Tx4l7plDYO9MkK+wCWdlJWzfVxiKAMKHYuKfl3bsXKVgzSTzNSD/BiyK9G/XyJHWDHtrMNFZ5rs12nWs01brZDQUY4M+qC6Dl4mwAJz0AZgYA+CXq6UYOV/Uv002bN6sz0ybktAUjXzR41tZoSPf7E9yJtmWXcx0x08TtmXDUH9TKNmr1iuZZoWsQw35eiIBNqKAEJElPihPagL8BriMuwBXHp2+ATjR+1+HDnELsEwnssVBNqRJ/VM/M0t4i9tOMNB9YkqoExzzW2QF67BeGYGeW8M+AJu7b9G/xqPWKED5shyxshyw6ZR95odGkassGCLE2CBwy3wbjmgNZVi9gCMvqAwxiWb2AjrlEdsCG8/wJ", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Resource = _t, #"Created On (PST)" = _t, #"TimeStamp (PST)" = _t, Start = _t, Stop = _t, #"Booking Status" = _t, Description = _t]),
#"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Start"}),
#"Filled Down" = Table.FillDown(#"Replaced Value",{"Start"}),
#"Grouped Rows" = Table.Group(#"Filled Down", {"Resource", "Start"}, {{"Stop", each List.Max([Stop]), type nullable text}}),
#"Changed Type" = Table.TransformColumnTypes(#"Grouped Rows",{{"Start", type datetime}, {"Stop", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Duration", each [Stop]-[Start], type duration)
in
#"Added Custom"
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.
- lbendlin2 years agoSuper User
Once you examined the code, replace the Source step with your own source.
- tracyhopaulson2 years agoResolver I
how do use your code but applying it to my real data source connecting to Dynamics?