Forum Discussion

Cazuc's avatar
Cazuc
Frequent Visitor
2 years ago
Solved

Take the last Row per category

Hello everyone!

 

First of all, I want to apolgize because of my English, because I am not a native speaker.

I have spending so much time trying to transform this:

 

TimestampUserTagSetReset
12/11/2023 6:24:51         User 1         Tag 1         Set Bypass         
12/11/2023 6:28:46User 1Tag 1Reset Bypass
12/11/2023 9:36:19User 1Tag 2Set Bypass
12/11/2023 10:00:05User 1Tag 2Reset Bypass
12/11/2023 14:56:23User 2Tag 8Set Bypass
12/11/2023 14:56:32User 2Tag 8Reset Bypass
12/11/2023 21:29:33User 3Tag 4Set Bypass
12/11/2023 21:30:23User 3Tag 4Reset Bypass
13/11/2023 0:09:45User 4Tag 1Set Bypass
13/11/2023 0:11:30User 4Tag 1Reset Bypass
14/11/2023 1:42:28User 4Tag 5Set Bypass
14/11/2023 1:48:10User 4Tag 6Set Bypass
14/11/2023 20:41:55User 5Tag 7Set Bypass
14/11/2023 21:13:55User 5Tag 7Reset Bypass

 

Into this:

 

TimestampUserTagSetReset
13/11/2023 0:11:30         User 4         Tag 1         Reset Bypass         
12/11/2023 10:00:05User 1Tag 2Reset Bypass
12/11/2023 21:30:23User 3Tag 4Reset Bypass
14/11/2023 1:42:28User 4Tag 5Set Bypass
14/11/2023 1:48:10User 4Tag 6Set Bypass
14/11/2023 21:13:55User 5Tag 7Reset Bypass
12/11/2023 14:56:32User 2Tag 8Reset Bypass

 

I only want to show the latest row of each tag.

I mean, only a number of rows corresponding with the number of the existent tags. Also, the row that it is shown must be the latest (taking timestamp).

 

Thank you very much!! I'm starting with Power Query 🙂

Need Help
  • slorin's avatar
    slorin
    2 years ago

    Hi,

    = Table.FromRecords(
       Table.Group(
          Prev_Step, 
          {"Tag"},
          {{"Data", each Table.Max(_,"Timestamp"), type record}}
        )[Data]
    )

    Stéphane

3 Replies

  • slorin's avatar
    slorin
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    = Table.FromRecords(
       Table.Group(
          Prev_Step, 
          {"Tag"},
          {{"Data", each Table.Max(_,"Timestamp"), type record}}
        )[Data]
    )

    Stéphane

    • Cazuc's avatar
      Cazuc
      Frequent Visitor
      2 years ago

      Hi Stéphane,

      First, I doubled the original table and I did what you posted:

       

      = Table.Group (#"Prev. step", {"Tag"}, {{"New Column Name", each List.Max([Timestamp]), type nullable datetime}})

       Then, I combined the new table with the previous one, with the common column "Timestamp". Then I expand the new merged table and selected the columns that I need (user and SetReset).

       

      If anyone has a better, more efficient way to do it I would be pleased to read.

       

      Thank you Stéphane!

  • Qasim_Jan's avatar
    Qasim_Jan
    Frequent Visitor
    2 years ago

    Give this a try:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdFLCsMgEAbgq0jWhTgPbTLLHqGPVcgii9Btqd309rUlMWqsEhAGvvmdyTA0gC1AixpJWUEWA2o9zaG5ufmpksp1uqeFy/xSp/djcm6rjoe8cSdsQ7+1jb/Psws+Z72QFehzhklojkCL9p8pqVoY+NH9O2l1uLiunvZThCVVS0MQ9OOFNFocV9O8Ih29MVb7NArO76MXDivhaP95WIzgG1dC+yzeNiKM/nfnzBSyEtQJ7LJsFaEWBjFhLLOoY12BAP1R6VzjBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Timestamp = _t, User = _t, Tag = _t, SetReset = _t]),
    #"Trimmed Text" = Table.TransformColumns(Source,{{"Tag", Text.Trim, type text}, {"User", Text.Trim, type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Trimmed Text", "Timestamp", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Timestamp.1", "Timestamp.2", "Timestamp.3", "Timestamp.4", "Timestamp.5", "Timestamp.6", "Timestamp.7", "Timestamp.8", "Timestamp.9", "Timestamp.10", "Timestamp.11"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Timestamp.3", "Timestamp.4", "Timestamp.5", "Timestamp.6", "Timestamp.7", "Timestamp.8", "Timestamp.9", "Timestamp.10", "Timestamp.11"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Columns",{"Timestamp.1", "Timestamp.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Grouped Rows" = Table.Group(#"Merged Columns", {"Tag"}, {{"Count", each _, type table [Merged=text, User=text, Tag=text, SetReset=nullable text]}, {"a", each List.Max([Merged]), type text}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Tag", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Custom", each Table.Last([Count],"Merged")),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Merged", "User", "SetReset"}, {"Merged", "User", "SetReset"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"Count", "a"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Merged", "Timestamp"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Timestamp", "User", "Tag", "SetReset"})
    in
    #"Reordered Columns"