Forum Discussion
Subindexing and Marginal Differencing
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 |
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
MaggieCommunity 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.
4 Replies
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
MaggieCommunity 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.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
MaggieCommunity 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.