Forum Discussion
tracyhopaulson
Resolver I
2 years agoCalculate 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
Resolver I
2 years agohope this works for you https://drive.google.com/file/d/1qTDip455X-M_SefingPvcL7gOaqr__4g/view?usp=drive_link
lbendlin
Super User
2 years agolet
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 ago
Super User
Once you examined the code, replace the Source step with your own source.
- tracyhopaulson2 years ago
Resolver I
how do use your code but applying it to my real data source connecting to Dynamics?