Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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"
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"
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
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.
I don't think this solution works very well for Power Query in Excel.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
48 | |
19 | |
13 | |
11 |