Difference between 2 times in the same column

[Anonymous]

Hi, 

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. 

[Anonymous]

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]

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

[Anonymous]

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

[Anonymous]

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) =>
                    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] <> 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"

[Anonymous]

@Anonymous So I am quite new to Power BI, so I apologize for the ignorance haha. Do I just create a custom column and paste all of that code into the box? 

[Anonymous]

https://youtu.be/Q48pyYLAPcc?t=74

[amitchandak]

@Anonymous , Try like

diff =
var _max = maxx(filter(Table, [date] = earlier([date]) && [time] < earlier([time]) && [material] = earlier([material])),[time])
return
[time] - maxx(filter(Table, [date] = earlier([date]) && [time] = _max && [material] = earlier([material])),[time])