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

I need to show if a new hire has been hired again, but also need to show if their name wasn't included in the new hire list provided to me. I am basically given two new hire list and one has a bunch of info with it including start dates and the other is pretty much just their name. If they are in the second list that has no info, but not in the first list, I need to keep it so I can get the rest of their info, but if they are in the first list multiple times, I need to keep all instances of their name in that list. 

wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
5 years ago

this might be a nestedjoin problem. you can provide some sample data of these two tables.