Forum Discussion
Solved
Duration between two dates.
My goal is to get the duration hours/ days between Keyout and Keyin. I tried to rearrange the table in Power Query but it didn't work. Is there a way to get the outpur using DAX or better Power ...
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Removed Columns" = Table.RemoveColumns(Source,{"ids"}), #"Grouped Rows" = Table.Group(#"Removed Columns", {"car", "user", "event"}, {{"All", each Table.AddIndexColumn(_,"Index",1,1)}}), #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"date_time", "Index"}, {"date_time", "Index"}), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded All", {{"car", type text}, {"Index", type text}}, "en-IN"),{"car", "user", "Index"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"), #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[event]), "event", "date_time"), #"Split Column by Delimiter" = Table.SplitColumn(#"Pivoted Column", "Merged", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Car", "User"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Car", Int64.Type}, {"User", type text}, {"KeyOut", type datetime}, {"KeyIn", type datetime}}), #"Added Custom1" = Table.AddColumn(#"Changed Type", "Current time", each DateTime.LocalNow() as datetime), #"Added Custom" = Table.AddColumn(#"Added Custom1", "Duration", each if [KeyIn]=null then [Current time] - [KeyOut] else [KeyIn]-[KeyOut]), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Duration", type duration}}) in #"Changed Type1"
Hi,
This M code works
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Removed Columns" = Table.RemoveColumns(Source,{"ids"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"car", "user", "event"}, {{"All", each Table.AddIndexColumn(_,"Index",1,1)}}),
#"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"date_time", "Index"}, {"date_time", "Index"}),
#"Pivoted Column" = Table.Pivot(#"Expanded All", List.Distinct(#"Expanded All"[event]), "event", "date_time"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"car", type text}, {"KeyOut", type datetime}, {"KeyIn", type datetime}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Current time", each DateTime.LocalNow() as datetime),
#"Added Custom" = Table.AddColumn(#"Added Custom1", "Duration", each if [KeyIn]=null then [Current time] - [KeyOut] else [KeyIn]-[KeyOut]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Duration", type duration}})
in
#"Changed Type1"Hope this helps.
almost...but some lines are split as below. they need to be on the same row.
could you take a look ?
I cannot understand. Show a representative sample and the expected result on that sample.
you M code is very close but i am not sure why the user "Matt" has separate lines...it should have keyout and key in on the same row as below right side .
