Forum Discussion
Extracting the duration from different rows
Hi, I'm trying to analyze access control data. I believe it's not complex, but I'm struggling until I get more familiar with Power BI. The data is in this format: What I want to extract...
Still working fine with me.
Maybe you can verify - Edit: in the Query Editor at step #"Added Custom4" - with the code below (adjust for your source) in which I created separate columns for each condition that must all be TRUE for the duration to be calculated. If at least one of the conditions is FALSE, Duration will be null.
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time", type time}, {"Action", type text}, {"Service", type text}, {"User", type text}, {"Server", type text}}), #"Inserted Merged Date and Time" = Table.AddColumn(#"Changed Type", "DateTime", each [Date] & [Time], type datetime), #"Added Index" = Table.AddIndexColumn(#"Inserted Merged Date and Time", "Original Sort", 0, 1), #"Sorted Rows" = Table.Sort(#"Added Index",{{"User", Order.Ascending}, {"Service", Order.Ascending}, {"DateTime", Order.Ascending}}), #"Added Index1" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1), #"Added Index2" = Table.AddIndexColumn(#"Added Index1", "Index.1", 1, 1), #"Merged Queries" = Table.NestedJoin(#"Added Index2",{"Index"},#"Added Index2",{"Index.1"}, "Previous",JoinKind.LeftOuter), #"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries", "Previous", {"Action", "User", "Service", "DateTime"}, {"Previous.Action", "Previous.User", "Previous.Service", "Previous.DateTime"}), #"Added Custom" = Table.AddColumn(#"Expanded Previous", "CheckUser", each [User] = [Previous.User]), #"Added Custom1" = Table.AddColumn(#"Added Custom", "CheckService", each [Service] = [Previous.Service]), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "CheckOut", each [Action] = "OUT"), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "CheckIn", each [Previous.Action] = "IN"), #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Duration", each if [CheckUser] and [CheckService] and [CheckOut] and [CheckIn] then [DateTime] - [Previous.DateTime] else null), #"Sorted Rows1" = Table.Sort(#"Added Custom4",{{"Original Sort", Order.Ascending}}), #"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows1",{"DateTime", "Action", "Duration", "User", "Service", "Server"}) in #"Removed Other Columns"
Thank you for your response Marcel,
I've used your code and I realized I need to use an additional column, 'Service', as the same user can be login into different services at the same time. I came with the following code:
let
Source = Excel.Workbook(File.Contents("D:\Access\access_control.xlsx"), null, true),
Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet2_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Time", type time}, {"Action", type text}, {"Service", type text}, {"User", type text}, {"Server", type text}}),
#"Inserted Merged Date and Time" = Table.AddColumn(#"Changed Type", "DateTime", each [Date] & [Time], type datetime),
#"Added Index" = Table.AddIndexColumn(#"Inserted Merged Date and Time", "Original Sort", 0, 1),
#"Sorted Rows" = Table.Sort(#"Added Index",{{"User", Order.Ascending}, {"Service", Order.Ascending}, {"DateTime", Order.Ascending}}),
#"Added Index1" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
#"Added Index2" = Table.AddIndexColumn(#"Added Index1", "Index.1", 1, 1),
#"Merged Queries" = Table.NestedJoin(#"Added Index2",{"Index"},#"Added Index2",{"Index.1"}, "Previous",JoinKind.LeftOuter),
#"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries", "Previous", {"Action", "User", "Service", "DateTime"}, {"Previous.Action", "Previous.User", "Previous.Service", "Previous.DateTime"}),
#"Added Custom" = Table.AddColumn(#"Expanded Previous", "Duration", each if [User] = [Previous.User] and [Service] = [Previous.Service] and [Action] = "OUT" and [Previous.Action] = "IN" then [DateTime] - [Previous.DateTime] else null),
#"Sorted Rows1" = Table.Sort(#"Added Custom",{{"Original Sort", Order.Ascending}}),
#"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows1",{"DateTime", "Action", "Duration", "User", "Service", "Server"})
in
#"Removed Other Columns"But still I get null in 'Duration'. Could you give me a hand with this?
Thanks.
Your code works fine with me.
Possibly your data have no matching combinations?
I aded an IN and an OUT for user A and Service B.
Funny...
First, thanks for your help. It still doesn't work for me, even when I simplify the source to:
Date Time Action User Service Server 01/03/2016 10:50:34 IN A A001 SERV1 01/03/2016 10:50:35 IN A A002 SERV1 01/03/2016 10:50:36 IN A A003 SERV2 01/03/2016 10:50:37 IN B A003 SERV2 01/03/2016 10:50:38 IN A A004 SERV1 01/03/2016 10:50:39 IN C A002 SERV1 01/03/2016 12:34:22 OUT A A001 SERV1 01/03/2016 12:34:24 OUT A A002 SERV1 01/03/2016 12:34:29 OUT A A003 SERV2 20/03/2016 10:50:34 OUT C A002 SERV1 20/03/2016 10:50:40 OUT B A003 SERV2 20/03/2016 10:50:50 OUT A A004 SERV1 I still receive null in Duration. I'm thinking something with Excel format or version (Excel 2010)???
Thanks.
Still working fine with me.
Maybe you can verify - Edit: in the Query Editor at step #"Added Custom4" - with the code below (adjust for your source) in which I created separate columns for each condition that must all be TRUE for the duration to be calculated. If at least one of the conditions is FALSE, Duration will be null.
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time", type time}, {"Action", type text}, {"Service", type text}, {"User", type text}, {"Server", type text}}), #"Inserted Merged Date and Time" = Table.AddColumn(#"Changed Type", "DateTime", each [Date] & [Time], type datetime), #"Added Index" = Table.AddIndexColumn(#"Inserted Merged Date and Time", "Original Sort", 0, 1), #"Sorted Rows" = Table.Sort(#"Added Index",{{"User", Order.Ascending}, {"Service", Order.Ascending}, {"DateTime", Order.Ascending}}), #"Added Index1" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1), #"Added Index2" = Table.AddIndexColumn(#"Added Index1", "Index.1", 1, 1), #"Merged Queries" = Table.NestedJoin(#"Added Index2",{"Index"},#"Added Index2",{"Index.1"}, "Previous",JoinKind.LeftOuter), #"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries", "Previous", {"Action", "User", "Service", "DateTime"}, {"Previous.Action", "Previous.User", "Previous.Service", "Previous.DateTime"}), #"Added Custom" = Table.AddColumn(#"Expanded Previous", "CheckUser", each [User] = [Previous.User]), #"Added Custom1" = Table.AddColumn(#"Added Custom", "CheckService", each [Service] = [Previous.Service]), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "CheckOut", each [Action] = "OUT"), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "CheckIn", each [Previous.Action] = "IN"), #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Duration", each if [CheckUser] and [CheckService] and [CheckOut] and [CheckIn] then [DateTime] - [Previous.DateTime] else null), #"Sorted Rows1" = Table.Sort(#"Added Custom4",{{"Original Sort", Order.Ascending}}), #"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows1",{"DateTime", "Action", "Duration", "User", "Service", "Server"}) in #"Removed Other Columns"Thank you again for your help Marcel,
That made the trick and I added some code to get rid of spaces, in case this was the issue too.