cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
saa3244 Frequent Visitor
Frequent Visitor

Subindexing and Marginal Differencing

I have this dataset:

UserDateTime
A9/1/20198:00 AM
A9/1/201910:00 AM
A9/1/20194:00 PM
A9/1/20195:00 PM
A9/2/20198:00 AM
A9/2/20195:00 PM
B9/1/20199:00 AM
B9/1/20193:00 PM
B9/1/20195:00 PM

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

UserDateTimeUser/Date Index 
A9/1/20198:00 AM1
A9/1/201910:00 AM2
A9/1/20194:00 PM3
A9/1/20195:00 PM4
A9/2/20198:00 AM1
A9/2/20195:00 PM2
B9/1/20199:00 AM1
B9/1/20193:00 PM2
B9/1/20195:00 PM3

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.

UserDateTimeUser/Date IndexMarginal Difference
A9/1/20198:00 AM102:00:00
A9/1/201910:00 AM206:00:00
A9/1/20194:00 PM301:00:00
A9/1/20195:00 PM4n/a
A9/2/20198:00 AM109:00:00
A9/2/20195:00 PM2n/a
B9/1/20199:00 AM106:00:00
B9/1/20193:00 PM202:00:00
B9/1/20195:00 PM3n/a
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Subindexing and Marginal Differencing

Hi @saa3244 

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"

Capture25.JPG

 

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

Capture26.JPG

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

Capture27.JPG

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.

 

 

View solution in original post

4 REPLIES 4
Community Support Team
Community Support Team

Re: Subindexing and Marginal Differencing

Hi @saa3244 

Create columns

Capture8.JPG

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.

saa3244 Frequent Visitor
Frequent Visitor

Re: Subindexing and Marginal Differencing

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

Community Support Team
Community Support Team

Re: Subindexing and Marginal Differencing

Hi @saa3244 

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"

Capture25.JPG

 

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

Capture26.JPG

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

Capture27.JPG

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.

 

 

View solution in original post

saa3244 Frequent Visitor
Frequent Visitor

Re: Subindexing and Marginal Differencing

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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,480)