cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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
v-juanli-msft
Community Support
Community Support

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"

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
v-juanli-msft
Community Support
Community Support

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"

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

Anonymous
Not applicable

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
Community Support
Community Support

Hi @Anonymous 

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.

Anonymous
Not applicable

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors