Forum Discussion

topazz11's avatar
topazz11
Helper III
3 years ago
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 ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    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"

Ashish_Mathur's avatar
Ashish_Mathur
Super User
3 years ago

I cannot understand.  Show a representative sample and the expected result on that sample.

topazz11's avatar
topazz11
Helper III
3 years ago

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 . 

 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    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"

    • topazz11's avatar
      topazz11
      Helper III
      3 years ago

      it doesnt work when the data is missing in either keyin or keyout

      duration value doesnt make sense.. could you please check it one more time with this dataset?

      here is new dataset

      pbix 

       

       

      thank you Ashish_Mathur