Difference between 2 times in the same column

Question

Hi,&nbsp;&nbsp;I am trying to create a new column that displays the how many minutes have passed between each row in the [Time] Column. Basically, I just need to subtract the bottom row from the above row for all rows. Here's what I have tried, but there is not enough memory to complete the operation. I am wondering if anyone knows a better way around this.&nbsp;&nbsp;&nbsp;

amitchandak · Answer

Anonymous , Try like
diff =var _max = maxx(filter(Table, [date] = earlier([date]) &amp;&amp; [time] &lt; earlier([time]) &amp;&amp; [material] = earlier([material])),[time])return [time] - maxx(filter(Table, [date] = earlier([date]) &amp;&amp; [time] = _max &amp;&amp; [material] = earlier([material])),[time])

anonymous · Answer

Yeah, I know a much better way. Do it in Power Query. Bear in mind that you should never use CALCULATE in a big table when you create a calculated column. This is because CALCULATE performs something that's known as 'context transition' and this operation in very costly. You've just experienced it...

anonymous · Answer

Anonymous&nbsp;Thanks for the input. How would I go about doing that in the power query editor?&nbsp;

anonymous · Answer

"Easily" is the answer 🙂 I'll post a solution to this.

anonymous · Answer

Here's the M code:let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdJLbsMwDATQu3idAEPqY3l2bXe9QpArdNf7dxQXThXVBLJQ4AeJHPJ2W96WywK7fn5/XR0O/bONqdKsH5f75T/iRohsMSmFaAExsDiRR+IDScyZyXVMJwSNpRElIOpIb+UUk6xfi0hTx/TnQ+9zRyrE/+YyE6xMG5ECoqZFvEYk09NE/JWoaRvLHaOrym2f0SlJLLbP6IxY6ZM2j0jWd2LVsQYPgR6S1m8p9SAfcy6VRWPEkctMlJtmEJJebtXiRQSPW2wkY7no+2LPrZtJj05jtIhsLCuBiKyPSeeAQPvU9nRPbzGqHIRE6/2bbj0j2l3tiwVEY+zRtYhoMXHccv8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Material = _t, Date = _t, Time = _t, Shift = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Material", type text}, {"Date", type date}, {"Time", type time}, {"Shift", type text}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Material", "Date", "Shift", "Time"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Material", Order.Ascending}, {"Date", Order.Ascending}, {"Shift", Order.Ascending}, {"Time", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Index",{"Index", "Material", "Date", "Shift", "Time"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns1",{{"Index", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(
        #"Changed Type1",
        "PrevTime",
        each List.First(
            Table.SelectRows(
                #"Changed Type1",
                (r) =&gt;
                    r[Material] = [Material]
                    and
                    r[Date] = [Date]
                    and
                    r[Shift] = [Shift]
                    and
                    r[Index] = [Index] - 1
            )[Time],
            null
        )
    ),
    #"Added Custom1" = Table.AddColumn(
        #"Added Custom", 
        "TimeDiff", 
        each
            if [PrevTime] &lt;&gt; null then
                // this rounds a number such as
                // 9.822 to just 9. You can remove
                // the Int32.From function to get
                // the exact number of minutes
                // with fractional parts.
                Int32.From(Duration.TotalMinutes([Time] - [PrevTime]))
            else
                null
        ),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"PrevTime", "Index"})
in
    #"Removed Columns"

Forum Discussion

Difference between 2 times in the same column

7 Replies

Related content

How to add two different calculated columns of two different table

Different schema into different destination

1. How to add two different calculated columns of two different table

Different Groupby Column Names in Summarize

Date difference between two different tables columns