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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
joelv67
Frequent Visitor

Combining rows and generating TimeSpan difference on date column

Application Module ExternalTaskId TaskType CreateDate

LocalAppMod11112/6/2016 12:01:00 PM
LocalAppMod11212/6/2016 12:09:00 PM
NetAppMod21112/6/2016 12:02:00 PM
NetAppMod21212/6/2016 12:08:00 PM
LocalAppMod12112/6/2016 12:05:00 PM
LocalAppMod12212/6/2016 12:06:00 PM

 

I have data similar to the above table. I would like to get a TimeSpan difference for each of the "unique" start,end times. Currently I don't have the 1st two "distinct" from the bottom two (other than the time), if needed I can add that, so that each grouping of the two are unique.

Can I combine each Two Rows so that I can get a diff between (example row 1 & row 2) for CreateDate?

1 ACCEPTED SOLUTION
MattAllington
Community Champion
Community Champion

There are normally better or worse approaches depending on what you are trying to do. But that aside, you could do this in Power Query (get data).  

 

Load the table

add an index column

create a custom column that is the index +1 (call it next)

save the query but don't load it

join the table to itself on the index = next column. 

 

When done, delete all the columns you don't need, including the Index columns



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Hi @joelv67,

 

I agree with MattAllingto's point of view, if you have a index column, you can directly use it to get the diff.

 

For example: "Tasktype" is the index column.

 

Diff(TaskType) = 
var currApplication= LASTNONBLANK(Sheet6[Application],[Application])
var diffSecond=DATEDIFF(MAXX(
							FILTER(ALL(Sheet6),
							Sheet6[Application]=currApplication&&
							Sheet6[ExternalTaskId]=MAX([ExternalTaskId])&&
							Sheet6[TaskType]<max([TaskType])),[CreateDate]),
						MAX([CreateDate]),SECOND)
return
if(diffSecond<> BLANK(),diffSecond,0)

 

 

If your records not contain the index column, you can use the date as the index.

 

Diff = 
var currApplication= LASTNONBLANK(Sheet6[Application],[Application])
var diffSecond=DATEDIFF(MAXX(
       FILTER(ALL(Sheet6),
       Sheet6[Application]=currApplication&&
       Sheet6[ExternalTaskId]=MAX([ExternalTaskId])&&
       Sheet6[CreateDate]<max([CreateDate])),[CreateDate]),
      MAX([CreateDate]),SECOND)
return
if(diffSecond<> BLANK(),diffSecond,0)

 

Result visual:

Capture.PNG

 

Regards,

Xiaoxin Sheng

Matt and Xiaoxin Sheng -

Thank you for your responses.

 

I was able to get Matt's solution to work.

 

My data sample was over simplified. I am not sure I can apply Xiaoxin Sheng's solution to my issue, but I will give it a try as well. The EventTypeId can not be the index and I don't think the CreateDate can as well, since multiple Application/Module combinations will be writing to the table and I am sure some Dates will collide.

 

But perhaps I can use the combination of creating an index column from Matt's suggestion with Xiaoxin Sheng's suggestion.

 

joelv

MattAllington
Community Champion
Community Champion

There are normally better or worse approaches depending on what you are trying to do. But that aside, you could do this in Power Query (get data).  

 

Load the table

add an index column

create a custom column that is the index +1 (call it next)

save the query but don't load it

join the table to itself on the index = next column. 

 

When done, delete all the columns you don't need, including the Index columns



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

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