Filter the earliest date based on each ID

[NickProp28]

Dear Community, 

Good Day!

Aprreciate if can provide me some help on Power Query Editor. How can I filter the earliest date in order to overcome the duplicate consolID issues. 

For example 'C00015706' , the earliest date is 23/1/2019.  And I only need this row. 

Here attached with the pbix: https://ufile.io/5c6q50oj

Any help will appreciated!

[Mohammad_Refaei · Accepted solution]

In advanced editor replace the code with this:

let
    Source = Excel.Workbook(File.Contents("C:\Users\nikchoo\Desktop\testing123.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Consol ID", type text}, {"ETD", type date}}),
    #"Sorted Rows" = Table.Buffer(Table.Sort(#"Changed Type",{{"ETD", Order.Ascending}})),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Consol ID"})
in
    #"Removed Duplicates"

The idea is simply sorting date in ascending order. Use the Table.Buffer to cache the sorting then remove duplicates.

Please check the sample file.

[TomMartens]

Hey @NickProp28 ,

personally I'm restricted to downloading files from onedrive, dropbox, or google drive only.

Nevertheless, a solution to your requirement should be straightforward.

Create a grouping transformation based on the column "Console ID". This article describes how to use this transformation: Grouping or summarizing rows | Microsoft Docs

Create a grouping that contains two aggregations

• Minimum, based on the date column
  This will represent the first date for each id
• All rows
  This holds all the columns for each row

Then expand the column that holds all the columns, be aware that also the column Console ID is contained. Create a custom column that checks if the column ETD equals the column with the Minimum date return 1 if true, otherwise 0.

Finally, you can filter all the rows that contain 1.

Maybe things can be more simple if you just want to remove duplicates, by using the Remove Duplicates transformation:

Hopefully, this provides what you are looking for.

Regards,

Tom