Forum Discussion
Get the time spent between two dates (first and last)
Hello everyone
I would like to be able to get the time between the date in cell 1 and the date of the last cell
Here is an example of my data.
The final result I would like to obtain is
Product A consumed X kWh in Y sec
Output exemple :
The most complicated part is to perform this calculation between the first timestamp cell and the last
Maybe it would be necessary to create another column that will calculate the time spent between cell N and N+1
I know it could be done in Excel but I have to use Power Query and I don't know enough about it
If you have an idea or a lead for a solution I'd love to hear from you !
Thank you in advance and have a nice day !
Hi Anonymous ,
Is it something like this you are after?
Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLUNTDRNTJSMDSxMrKwMjBQ0lEyNDIFko5KsToY8oYQeSNc8kZgeWOc+o3B8iYmuORNwPKmFrjkTfHabwlzP1R/LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Timestamp = _t, kWh = _t, ProductId = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Timestamp", type datetime}, {"kWh", Int64.Type}, {"ProductId", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ProductId"}, {{"MinTimestamp", each List.Min([Timestamp]), type nullable datetime}, {"MaxTimestamp", each List.Max([Timestamp]), type nullable datetime}, {"SumkWh", each List.Sum([kWh]), type nullable number}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "DurationInSeconds", each Duration.TotalSeconds ( [MaxTimestamp] - [MinTimestamp] )) in #"Added Custom"Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
6 Replies
- Vijay_A_Verma
Most Valuable Professional
What is meant by last here - Is it row 7 or is it the previous cell?
If you can show us the output as well, then it would be easier to give the solution.
let Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdDNDYAgDAbQVUzPJpT+KHjTNQj7ryGIARXipZdXvraEAGQNiiEkmqxs5GAGS5rqDnFOTAOmxu7FPjPnpqOw71lsY/0PX/rXV9PN62C2tnDuw/lxmPQsUplxsLnUcEy/pt/VuHA8AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [timestamp = _t, kwh = _t, productid = _t]), #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"timestamp", type datetime}, {"kwh", Int64.Type}, {"productid", type text}}), #"Raggruppate righe" = Table.Group(#"Modificato tipo", {"productid"}, {{"all", each Text.Combine({"d"}&List.Transform({[timestamp]{0},List.Last([timestamp])}, Text.From),"alle ")}, {"totKwh", each List.Sum([kwh]), type nullable number}}) in #"Raggruppate righe"- tackytechtom
Hi Anonymous ,
Is it something like this you are after?
Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLUNTDRNTJSMDSxMrKwMjBQ0lEyNDIFko5KsToY8oYQeSNc8kZgeWOc+o3B8iYmuORNwPKmFrjkTfHabwlzP1R/LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Timestamp = _t, kWh = _t, ProductId = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Timestamp", type datetime}, {"kWh", Int64.Type}, {"ProductId", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ProductId"}, {{"MinTimestamp", each List.Min([Timestamp]), type nullable datetime}, {"MaxTimestamp", each List.Max([Timestamp]), type nullable datetime}, {"SumkWh", each List.Sum([kWh]), type nullable number}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "DurationInSeconds", each Duration.TotalSeconds ( [MaxTimestamp] - [MinTimestamp] )) in #"Added Custom"Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
