Forum Discussion

nic2023's avatar
nic2023
Frequent Visitor
1 year ago
Solved

Remove duplicates based on conditions in Power Query

Hi everyone, I'm trying to remove some duplicates in a large list, which is formed from appending 2 lists of staff IDs. Some of the Staff IDs have duplicate entries, I'd like to set up some conditio...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    1 year ago

    Hi,

    One of ways is using Table.Group function in Power Query Editor.

     

    let
    Source = Data_Source,
    #"Grouped Rows" = Table.Group(Source, {"Staff ID"}, {{"End Date", each List.Max([End Date]), type nullable date}})
in
    #"Grouped Rows"

     

     

Kedar_Pande's avatar
Kedar_Pande
Super User
1 year ago

nic2023 

Power Query Code:

let
Source = YourTableName,
SortTable = Table.Sort(Source, {{"Staff ID", Order.Ascending}, {"End Date", Order.Descending}}),
RemoveDuplicates = Table.Distinct(SortTable, {"Staff ID"})
in
RemoveDuplicates

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn