A complex remove duplicates. Please help.

[Anonymous]

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 both columns New Hire Name and Start Date and hit remove duplicates it will keep all of them. If I only use the New Hire name column. It will delete lines 2 and 3. I am pretty new to DAX and power query, but is there a way to do this?

[v-alq-msft · Accepted solution]

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]

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)

[edhans]

Hi @Anonymous 

without more specifics hard to say, but take a look at this. It turns this:

into this:

I used this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyIt3yU9V0lEy1DfUNzIwMlCK1UERN8UhnleakwMRS8xLRRGLBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"New Hire Name" = _t, #"Start Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}}),
    #"Grouped Rows" = 
        Table.Group(
            #"Changed Type", 
            {"New Hire Name"}, 
            {
                {"AllRows", 
                each if Table.RowCount(_) = 1 then _ else
                    Table.Distinct(
                        Table.SelectRows(_, each [Start Date] <> null)
                    ), 
                type table [New Hire Name=nullable text, Start Date=nullable date]
                }
            }
        ),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Start Date"}, {"Start Date"})
in
    #"Expanded AllRows"

The logic I used was this:

• Group all data by the New Hire Name
• Count the rows in the nested tables. If there is just one record keep it.
• If there are 2+ records, remove any rows with null, then do a Table.Distinct() over that result to remove any duplicate records. So if John Doe had two rows that were 5/1/2020, my logic would keep only one of those.
• Expanded the Start Date column again.

If that isn't what you want, please be more specific on how the logic should work.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[Fowmy]

@Anonymous 

What is your logic to remove the record?

Do want to remove the lines for new hire names that have at least one record with start date?

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

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]

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

[camargos88]

@Anonymous ,

It doesn't like you need to remove the duplicated, actually you don't have any.

Does it work if you remove when there is more than 1 row for the New Hire column and delete the null row ?

[Anonymous]

I am not sure I understand your response.