Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
laganlee
Helper II
Helper II

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!

1 ACCEPTED SOLUTION
jennratten
Super User
Super User

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.

jennratten_0-1667577852529.png

 

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"

 

View solution in original post

4 REPLIES 4
AntrikshSharma
Community Champion
Community Champion

@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

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
Super User
Super User

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.

jennratten_0-1667577852529.png

 

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"

 

Hi

That is fantastic! Many thanks 🙂

So simple.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors