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

v-yingjl's avatar
v-yingjl
Icon for Community Support rankCommunity Support
6 years ago

Hi amalm ,

It is difficult to achieve this directly. Because there is no logic to determine when the user “forgot to log out” after logging in, for example, why log out after user1 log in at 7 am cannot be at 4 pm but must be a null value. Of course we know he is 'Forgot to log out', but the computer has no way to judge this logic to achieve the so-called correspondence between rows when the rows of the two tables are not equal.
One approach is to manually add the index column between the two tables, as shown in the following figure, to force the row number to be equal and use merge to merge the two tables. The disadvantage is that this will be quite complicated when there is a lot of data.

Merge tables based on index column:

Expand and remove unnecessary columns, adjust column position and rename columns to show the result:

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

 

  • 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