Subindexing and Marginal Differencing

[Anonymous]

I have this dataset:

User	Date	Time
A	9/1/2019	8:00 AM
A	9/1/2019	10:00 AM
A	9/1/2019	4:00 PM
A	9/1/2019	5:00 PM
A	9/2/2019	8:00 AM
A	9/2/2019	5:00 PM
B	9/1/2019	9:00 AM
B	9/1/2019	3:00 PM
B	9/1/2019	5:00 PM

The first thing I want to do is add a subindex on User and Date.

User	Date	Time	User/Date Index
A	9/1/2019	8:00 AM	1
A	9/1/2019	10:00 AM	2
A	9/1/2019	4:00 PM	3
A	9/1/2019	5:00 PM	4
A	9/2/2019	8:00 AM	1
A	9/2/2019	5:00 PM	2
B	9/1/2019	9:00 AM	1
B	9/1/2019	3:00 PM	2
B	9/1/2019	5:00 PM	3

The second thing I want is a column that takes the difference between times in its row (A) and the row after it (A+1). "n/a" means I don't really care about this value. I just want the interday marginal differences.

User	Date	Time	User/Date Index	Marginal Difference
A	9/1/2019	8:00 AM	1	02:00:00
A	9/1/2019	10:00 AM	2	06:00:00
A	9/1/2019	4:00 PM	3	01:00:00
A	9/1/2019	5:00 PM	4	n/a
A	9/2/2019	8:00 AM	1	09:00:00
A	9/2/2019	5:00 PM	2	n/a
B	9/1/2019	9:00 AM	1	06:00:00
B	9/1/2019	3:00 PM	2	02:00:00
B	9/1/2019	5:00 PM	3	n/a

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

It is possible with Power query. But it is more complicated then my previous answer.

In original table "Table"

1. add an index column by grouped data

https://www.myonlinetraininghub.com/numbering-grouped-data-power-query

2. merge columns to get a new column "Merged1"

3. create a blank query, paste code in Advanced editor

let
    Source =Table,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Custom.new index] <> 1)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each [Custom.new index]-1),
    #"Inserted Merged Column" = Table.AddColumn(#"Added Custom", "Merged2", each Text.Combine({[User], Text.From([Date], "en-US"), Text.From([Custom], "en-US")}, " "), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"Custom.new index", "Merged1"})
in
    #"Removed Columns"

Reference:

https://community.powerbi.com/t5/Desktop/Move-one-row-up-in-a-particular-column/td-p/624373

4. merge queries to a new queries, add a custom column

let
    Source = Table.NestedJoin(Table, {"Merged1"}, Query1, {"Merged2"}, "Query1", JoinKind.LeftOuter),
    #"Expanded Query1" = Table.ExpandTableColumn(Source, "Query1", {"Custom.Time"}, {"Query1.Custom.Time"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Query1",{"Merged1"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Custom.2", each if [Query1.Custom.Time] <> null then [Query1.Custom.Time]-[Custom.Time] else "n/a"),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom.2", type text}})
in
    #"Changed Type"

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.

[Anonymous]

I've accepted this as a solution for Marginal Differencing. However, I still would like to be able to determine which time is odd and even based on user and date.

User   Date   Time    Index

1         12/1   3:00    1

1         12/1   4:00    2

1         12/1   5:00    3

2         12/1   3:00    1

2         12/2   3:00    1

[v-juanli-msft]

Hi @Anonymous 

Create columns

rank index =
RANKX (
    FILTER (
        'Table',
        'Table'[User] = EARLIER ( 'Table'[User] )
            && 'Table'[Date] = EARLIER ( 'Table'[Date] )
    ),
    [Time],
    ,
    ASC,
    DENSE
)


Marginal Difference =
VAR next =
    CALCULATE (
        MAX ( 'Table'[Time] ),
        FILTER (
            'Table',
            'Table'[User] = EARLIER ( 'Table'[User] )
                && 'Table'[Date] = EARLIER ( 'Table'[Date] )
                && [rank index]
                    = EARLIER ( 'Table'[rank index] ) + 1
        )
    )
RETURN
    IF ( next = BLANK (), "n/a", FORMAT ( [Time] - next, "Medium Time" ) )

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.

[Anonymous]

I don't think this solution works very well for Power Query in Excel.