Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

A complex remove duplicates. Please help.

Example data: Row # New Hire Name Start Date 1. John_Doe 1/1/2020 2. John_Doe 5/1/2020 3. John_Doe null 4. Jane_Doe null   I need to remove line 3, but if I select bo...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may add a new step like below.

    = Table.SelectRows(#"Changed Type",each 
let x = [New Hire Name],
y=Table.RowCount(
    Table.SelectRows(#"Changed Type",each [New Hire Name]=x)
)
in
(y>1 and [Start Date]<>null) or y=1
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

You could just use the Fill Down function in the GUI on the Date column, and then your next step would be Table.Distinct(PriorStep)