Forum Discussion

laganlee's avatar
laganlee
Icon for Helper II rankHelper II
3 years ago
Solved

Delete row(s) with matching IDs with earlier dates.

Hi all.

I would like to delete rows with the same key IDs but with earlier dates; I want to keep the latest row.

The reason for this is I want the latest record.

For example

ID       Type              Date

21       Initial           03 July 2022            **** Delete this row ****

21       Updated      04 October 2022

54       Initial           02 June 2022

54       Corrected    05 October 2022     **** Delete this row ****

55       Initial           02 June 2022

56       Initial           02 June 2022

 

Many thanks for looking!

Need Help
  • jennratten's avatar
    jennratten
    3 years ago

    Hello - you can accomplish this by grouping the records by the max date and then retrieving the type for that date, like in the example below.  Note, in your description I think you flagged the wrong row to delete for ID 54.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjJU0lHyzMssScwBMsz1jfWNDIyMlGJ1oFKhBSmJJakpQJahgb4JQtLUBFmfmb4RmpRzflFRajJcpymStClunWbYpWIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Type", type text}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Date", each List.Max([Date]), type nullable date}, {"Data", each _, type table [ID=nullable number, Type=nullable text, Date=nullable date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Type", each [Data][Type]{List.PositionOf ( [Data][Date], [Date] )}, type text ),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"})
in
    #"Removed Columns"

     

4 Replies

  • AntrikshSharma's avatar
    AntrikshSharma
    Icon for Community Champion rankCommunity Champion
    3 years ago

    laganlee You can use this:

    let
    Source = 
        Table.FromRows (
            Json.Document (
                Binary.Decompress (
                    Binary.FromText (
                        "i45WMjJU0lFS8MzLLMlMzAEyzfWN9Y0MjIyUYnVgkqEFKYklqSlApqGBvglC1tQEVauZvhG6pHN+UVFqMlyzKZK8KT7NZrgkYwE=",
                        BinaryEncoding.Base64
                    ),
                    Compression.Deflate
                )
            ),
            let
                _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
            in
                type table [ ID = _t, Type = _t, Date = _t ]
        ),
    ChangedType = 
        Table.TransformColumnTypes (
            Source,
            { { "ID", Int64.Type }, { "Type", type text }, { "Date", type date } }
        ),
    GroupedRows = 
        Table.Group (
            ChangedType,
            { "ID" },
            {
                {
                    "Group",
                    ( CurrentGroup ) =>
                        let
                            RowCount = Table.RowCount ( CurrentGroup ),
                            FirstDate = List.Min ( CurrentGroup[Date] ),
                            Check =
                                if RowCount >= 2 
                                then Table.SelectRows ( CurrentGroup, each [Date] <> FirstDate )
                                else CurrentGroup
                        in
                            Check,
                    type table [ ID = nullable Int64.Type, Type = nullable text, Date = nullable date ]
                }
            }
        ),
    RemovedColumns = Table.RemoveColumns ( GroupedRows, { "ID" } ),
    ExpandedGroup = 
        Table.ExpandTableColumn (
            RemovedColumns,
            "Group",
            { "ID", "Type", "Date" },
            { "ID", "Type", "Date" }
        )
in
    ExpandedGroup
    • laganlee's avatar
      laganlee
      Icon for Helper II rankHelper II
      3 years ago
      Hi Antriksh Sharma
      Many thanks for that - it is very much appreciated!
      It has shown me a really good way of solving that.
      I only accepted the other one as a solution as I could quickly use the cog wheel for groupings.
      Many thanks
  • jennratten's avatar
    jennratten
    Icon for Super User rankSuper User
    3 years ago

    Hello - you can accomplish this by grouping the records by the max date and then retrieving the type for that date, like in the example below.  Note, in your description I think you flagged the wrong row to delete for ID 54.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjJU0lHyzMssScwBMsz1jfWNDIyMlGJ1oFKhBSmJJakpQJahgb4JQtLUBFmfmb4RmpRzflFRajJcpymStClunWbYpWIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Type", type text}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Date", each List.Max([Date]), type nullable date}, {"Data", each _, type table [ID=nullable number, Type=nullable text, Date=nullable date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Type", each [Data][Type]{List.PositionOf ( [Data][Date], [Date] )}, type text ),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"})
in
    #"Removed Columns"

     

    • laganlee's avatar
      laganlee
      Icon for Helper II rankHelper II
      3 years ago

      Hi

      That is fantastic! Many thanks 🙂

      So simple.