Forum Discussion
Post PartisanFilter the earliest date based on each ID
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!
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.
2 Replies
- TomMartens
Super User
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
- Minimum, based on the date column
- Mohammad_Refaei
Solution Specialist
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.
