Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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.

 

power bi image.PNG

4 REPLIES 4
v-juanli-msft
Community Support
Community Support

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)

Capture4.JPG

 

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

Capture5.JPG

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
Resident Rockstar
Resident Rockstar

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
Not applicable

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?

what is the code you have entered?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.