Forum Discussion

NickProp28's avatar
NickProp28
Post Partisan
5 years ago
Solved

Filter 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 exampl...
Need Help
  • Mohammad_Refaei's avatar
    Mohammad_Refaei
    5 years ago

    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.

Mohammad_Refaei's avatar
Mohammad_Refaei
Solution Specialist
5 years ago

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.