Forum Discussion
Splitting table rows from value
Hello, I need to calculate the duration between Events for each particular user working in a specific file in a datasource that contains info from multiple users. Sample table below. The goal...
Try
DateDiff = 'view-activated collected'[DateTime] - MINX(FILTER('view-activated collected', 'view-activated collected'[DocPath] = EARLIER('view-activated collected'[DocPath]) && 'view-activated collected'[Username] = EARLIER('view-activated collected'[Username]) && 'view-activated collected'[DateTime] > EARLIER('view-activated collected'[DateTime]) ),'view-activated collected'[DateTime])
Anonymous -
You can do in Power Query as well:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDSN7TQNzIwMlAwNLEyMAAiJR0lx7w8EAnEbpk5qf4FqSCuLhAbQJXE6qBrNcTQGpaZWu6YXJJZlliSmgLk+6SWpeYoGCnoKoQHeQP5hjjNMkIxy4kUZxhjaMXuDGOwKaY4TDGBmuKUSnI4mGJoJRQOBtCwQzfLFB4d+MxKT81LARtijMMQzIjxTMlJhfsDlzbsceCck1+cSiAMTOGRgBp8xOk1wZoEYXqRtcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [DateTime = _t, Username = _t, FileName = _t, EventName = _t, ViewName = _t, Duration = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DateTime", type text}, {"Username", type text}, {"FileName", type text}, {"EventName", type text}, {"ViewName", type text}, {"Duration", type time}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Username", "FileName"}, {{"Grouped", each _, type table [DateTime=text, Username=text, FileName=text, EventName=text, ViewName=text, Duration=time]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "AddIndex", each Table.AddIndexColumn([Grouped],"Index",1,1)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each let tblName = [AddIndex] in Table.AddColumn([AddIndex],"NextDateTime", each tblName{[Index]}[DateTime])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"DateTime", "EventName", "ViewName", "Duration", "NextDateTime"}, {"DateTime", "EventName", "ViewName", "Duration", "NextDateTime"}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Expanded Custom", {{"NextDateTime", null}}),
#"Removed Columns" = Table.RemoveColumns(#"Replaced Errors",{"Grouped", "AddIndex"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"DateTime", type datetime}, {"NextDateTime", type datetime}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type1", "DurationCalculated", each Duration.TotalMinutes([NextDateTime]-[DateTime]))
in
#"Added Custom2"