Forum Discussion

joelv67's avatar
joelv67
Frequent Visitor
9 years ago
Solved

Combining rows and generating TimeSpan difference on date column

Application Module ExternalTaskId TaskType CreateDate LocalApp Mod1 1 1 12/6/2016 12:01:00 PM LocalApp Mod1 1 2 12/6/2016 12:09:00 PM NetApp Mod2 1 1 12/6/2016 12:02:00 PM Ne...
  • MattAllington's avatar
    MattAllington
    9 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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:

 

Regards,

Xiaoxin Sheng

joelv67's avatar
joelv67
Frequent Visitor
9 years ago

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