Forum Discussion
Post PartisanFilter the earliest date based on each ID
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.
Super UserHey 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