Nested Index Column & Referencing Previous Row

[thampton]

Hello, i have a two part inquiry: 

I have a table similar to what is below. It has a sequence column that acts as an index, but i would like to create a new index column to catch duplicate actions that show up on separate rows. These are the rows below in red text. Is this possible? 

User	Action	Date Time	Sequence	Desired Index	Duration
John	A	1/1/19 1:00	0	0	0
John	B	1/1/19 2:00	1	1	60
John	C	1/1/19 3:00	2	2	60
John	C	1/1/19 3:05	3	2	5
John	D	1/1/19 4:00	4	3	55

2. Is there a way to create a duration column to show time between the steps. All of these will have the same ID field, so i would like to see duration between each step from start to end. 

Thanks!

[v-cherch-msft · Accepted solution]

Hi @thampton 

You may create two calculated columns as below:

Desired Index =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Action] ),
    FILTER ( 'Table', 'Table'[Sequence] <= EARLIER ( 'Table'[Sequence] ) )
) - 1

Duration =
DATEDIFF (
    CALCULATE (
        MAX ( 'Table'[Date Time] ),
        FILTER ( 'Table', 'Table'[Sequence] = EARLIER ( 'Table'[Sequence] ) - 1 )
    ),
    'Table'[Date Time],
    MINUTE
) + 0

Regards,

[Zubair_Muhammad · Accepted solution]

@thampton 

Check the M / Power Query solution as well

File attached as well

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUXIEYgUTYx0TMzM9AxNDMxAwVwAKGijF6sBVOSGrsjCGAJAqQ2RVzocWIKkzNDI1AAOQOiN86ixMzI2AAKTOGFmdC7IqMzOE20yUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [User = _t, Action = _t, #"Date Time" = _t, Sequence = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date Time", type number}, {"Sequence", Int64.Type}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Date Time", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Action"}, {{"All", each _, type table}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1),
    #"Expanded All" = Table.ExpandTableColumn(#"Added Index", "All", {"User", "Date Time", "Sequence"}, {"User", "Date Time", "Sequence"}),
    #"Added Custom" = Table.AddColumn(#"Expanded All", "Custom", each let myuser=[User],myseq=[Sequence] in Duration.TotalMinutes(
[Date Time]-  (try Table.SelectRows(#"Expanded All",each [User]=myuser and [Sequence]=myseq-1)[Date Time]{0} otherwise [Date Time])))
in
    #"Added Custom"

[v-cherch-msft · Accepted solution]

Hi @thampton 

You may create two calculated columns as below:

Desired Index =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Action] ),
    FILTER ( 'Table', 'Table'[Sequence] <= EARLIER ( 'Table'[Sequence] ) )
) - 1

Duration =
DATEDIFF (
    CALCULATE (
        MAX ( 'Table'[Date Time] ),
        FILTER ( 'Table', 'Table'[Sequence] = EARLIER ( 'Table'[Sequence] ) - 1 )
    ),
    'Table'[Date Time],
    MINUTE
) + 0

Regards,