Get the time spent between two dates (first and last)

[Anonymous]

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 !

[tackytechtom · Accepted solution]

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/

[Anonymous]

Yes sir ! I think you have it ! 
That's what I was trying to do ! Thank you for your help, it helps me a lot. 😊


Big thanks ! 

[Anonymous]

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"

[Anonymous]

Wow, this is a complex function !
I need some time to understand what this function does ! 😅

But thanks for the answer !

[Vijay_A_Verma]

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. 

[Anonymous]

First, thank you for your answer.

Yes, in this case it is the 7th cell. (But in reality I don't know the number of cells and therefore the last one could be cell 18 or 25, ...)

Here is the result I would like to obtain: