M Query to return time difference between two cells in the same column

[Anonymous]

Hi

I am looking for a M query to return the difference in times between two cells in the same column. I have been able to do this a couple other ways (DAX formulas and creating two different tables with index values), but keep getting the error of not enough memory. My table is 3 million rows and I need all of the data so I cannot remove anything. Does anyone have a way around this? Im thinking M query would be the way to go but have not been able to create one.

[v-juanli-msft]

Hi @Anonymous 

Which method do you use?

from this statement:

I have been able to do this a couple other ways (DAX formulas and creating two different tables with index values)

It seems you use a complex method including creating another table.

But i used to slove this kind of problem with simple method below:

create calculated columns:

Column_Previous = CALCULATE(MAX(Sheet2[time]),FILTER(Sheet2,Sheet2[time]<EARLIER(Sheet2[time])))

diff_s = DATEDIFF([Column_Previous],[time],SECOND)

Or you could create a measure (measure will be better for performance generally, but measures can't be added into slicer visual)

Measure =
VAR Previous =
    CALCULATE (
        MAX ( Sheet2[time] ),
        FILTER ( ALLSELECTED ( Sheet2 ), Sheet2[time] < MAX ( Sheet2[time] ) )
    )
RETURN
    DATEDIFF ( Previous, MAX ( Sheet2[time] ), SECOND )

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sturlaws]

Hi @Anonymous,

Assuming that the rows are in the correct order, create an index on your table(unless you have something else in your dataset that uniquely identifies your rows which is also a number, then you can use this). This index is used to identify the previous row. You create it by Add Column -> Index Column. 

Then create a custom column with a code like this(note that theM is case sensitive.):

= if 
   #"Added Index"{[Index]} [Index] = 0 then null 
else 
   #"Added Index"{[Index]} [dummyDateTime] - #"Added Index"{[Index]-1} [dummyDateTime]

This code generates a new column where the values are the value of the current row - the value of the previous row. The if-statement is there to prevent an error for the first row where there is no prior row to compare with.

I have created a mockup report here: mockup 

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[Anonymous]

I get the following error when putting in this query.

"Expression.Error: The field 'date' of the record wasnt found."

Not sure why Im getting this. Are the other columns in the table interferring with this query?

[sturlaws]

what is the code you have entered?