Forum Discussion
Solved
Calculate Time
How do I calculate total time between start time and stop time when my data is like this?
Once you examined the code, replace the Source step with your own source.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Still need help please!
Apologies, cannot work off screenshots.
hope this works for you https://drive.google.com/file/d/1qTDip455X-M_SefingPvcL7gOaqr__4g/view?usp=drive_link
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.