Forum Discussion

amalm's avatar
amalm
Icon for Helper III rankHelper III
6 years ago
Solved

Making a merged table for Login-Logout

Hi, My data is in 2 tables, one for login and the other for logout. The format is as below:   I want the resulting table to be in this format:   As you can see, I want each login to ...
Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    Here is one way to do this.  Below are 3 M queries with similar data as an example.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below (X3).  The first two are just some example tables like your data.

     

    // call this query "login"
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJQ0lEytzIwACIFR18gJ7Q4tcgQSBsaGCjF6qCos0RXZwSkjeDqjPCaZ4ypDsM8kDoTkLpYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Time = _t, Username = _t, Param1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time", type time}, {"Username", type text}, {"Param1", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Event", each "Login")
in
    #"Added Custom"

//call this query "logout"
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJQ0lEytzI0sDIwUHD0BXJCi1OLDIG0oYGBUqwOijqgKmNUhUZA2hSqEMiEIog2I5g2SysjTOPNQLpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Time = _t, Username = _t, Param1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time", type time}, {"Username", type text}, {"Param1", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Date] <> null)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Event", each "Logout")
in
    #"Added Custom"

//call this query what you want
let
    Source = Table.Combine({login, logout}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Event", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Username", Order.Ascending}, {"Date", Order.Ascending}, {"Time", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "ForPivot", each if [Event] = "Login" then [Index] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"ForPivot"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down", "Records", each #"Filled Down"{[Index]}),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Event", "ForPivot", "Records"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Event]), "Event", "Records"),
    #"Expanded Login" = Table.ExpandRecordColumn(#"Pivoted Column", "Login", {"Date", "Time", "Username", "Param1"}, {"Date", "Time", "Username", "Param1"}),
    #"Expanded Logout" = Table.ExpandRecordColumn(#"Expanded Login", "Logout", {"Time", "Param1"}, {"Time.1", "Param1.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Logout",{"ForPivot"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}, {"Time", type time}, {"Username", type text}, {"Param1", Int64.Type}, {"Time.1", type time}, {"Param1.1", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Time", "Time In"}, {"Time.1", "Time Out"}, {"Param1", "Param1 (in)"}, {"Param1.1", "Param1 (out)"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Date", "Username", "Time In", "Time Out", "Param1 (in)", "Param1 (out)"})
in
    #"Reordered Columns"

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

amalm's avatar
amalm
Icon for Helper III rankHelper III
6 years ago

Thanks for the replies. 

The tables that I had provided are oversimplifications of the actual data that I have. 

 

v-yingjl the solution you provided won't work for us, because there is some other complexity involved. 

However, there is one way I can think of, to derive the logic for this requirement: The values for Param1 (for each user) will always be ascending, there is no chance for it to reduce over a period of time. 

 

So we can know if someone has forgotten to login/logout when there are 2 of either in a sequence without the corresponding other. 

 

In this case, how can I make the resulting table? 

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    Here is one way to do this.  Below are 3 M queries with similar data as an example.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below (X3).  The first two are just some example tables like your data.

     

    // call this query "login"
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJQ0lEytzIwACIFR18gJ7Q4tcgQSBsaGCjF6qCos0RXZwSkjeDqjPCaZ4ypDsM8kDoTkLpYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Time = _t, Username = _t, Param1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time", type time}, {"Username", type text}, {"Param1", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Event", each "Login")
in
    #"Added Custom"

//call this query "logout"
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJQ0lEytzI0sDIwUHD0BXJCi1OLDIG0oYGBUqwOijqgKmNUhUZA2hSqEMiEIog2I5g2SysjTOPNQLpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Time = _t, Username = _t, Param1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time", type time}, {"Username", type text}, {"Param1", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Date] <> null)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Event", each "Logout")
in
    #"Added Custom"

//call this query what you want
let
    Source = Table.Combine({login, logout}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Event", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Username", Order.Ascending}, {"Date", Order.Ascending}, {"Time", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "ForPivot", each if [Event] = "Login" then [Index] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"ForPivot"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down", "Records", each #"Filled Down"{[Index]}),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Event", "ForPivot", "Records"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Event]), "Event", "Records"),
    #"Expanded Login" = Table.ExpandRecordColumn(#"Pivoted Column", "Login", {"Date", "Time", "Username", "Param1"}, {"Date", "Time", "Username", "Param1"}),
    #"Expanded Logout" = Table.ExpandRecordColumn(#"Expanded Login", "Logout", {"Time", "Param1"}, {"Time.1", "Param1.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Logout",{"ForPivot"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}, {"Time", type time}, {"Username", type text}, {"Param1", Int64.Type}, {"Time.1", type time}, {"Param1.1", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Time", "Time In"}, {"Time.1", "Time Out"}, {"Param1", "Param1 (in)"}, {"Param1.1", "Param1 (out)"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Date", "Username", "Time In", "Time Out", "Param1 (in)", "Param1 (out)"})
in
    #"Reordered Columns"

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

    • amalm's avatar
      amalm
      Icon for Helper III rankHelper III
      6 years ago

      This is excellent, thank you so much!

      I'll let you know if I run into problems with the actual data.