Removing duplicates but maintaining one of the columns that is different

[cah2035]

Hello. I am trying to remove duplicates from my table using only a certain amount of columns, and I know that one column contains different information between two duplicates. I want to be able to have a single row of the duplicated information and have each non-duplicated data as either two columns or, even better, concatenate them into one cell. 

Below is an easy example. Amanda actually has TWO favorite ice cream flavors, and because of that, appears on two different rows. I want to remove duplicate people based on Name, Age, and Date because I only ever want one entry for those, but I want to keep the information about Amanda having two favorite flavors of ice cream. 

Backgroup: I am trying to curate a mess of data from a bunch of different tables that we had been keeping for years for the purpose of putting it in an actually relational database.

Name	Age	Hair Color	Fav Ice Cream
Amanda	25	Brown	Chocolate
Amanda	25	Brown	Strawberry
John	31	Red	Chocolate
Peter	35	Gray	Vanilla

Name	Age	Fav Color	Fav Ice Cream 1	Fav Ice Cream 2
Amanda	25	Brown	Chocolate	Strawberry
John	31	Red	Chocolate	null
Peter	35	Gray	Vanilla	null

[edhans · Accepted solution]

Hi @cah2035 , 

Give this a shot. It transforms this:

into this:

If you still want the flavors in 2+ columns, you can just split those columns by the comma delimiter.

Here is the code that does it. It basically is a grouping by the name, age, and hair color, then it combines the flavors into a list separated by commas. Put this in a blank query following the directions at the bottom.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsxNzEtJVNJRMjIFEk5F+eV5QNo5Iz85PyexJFUpVgeXmuCSosTypNSiokqwIq/8DJCosSGQCEpNwTAkILUktQikAGSGe1FiJZAKS8zLzMlJVIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Age = _t, #"Hair Color" = _t, #"Fav Ice Cream" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Age", Int64.Type}, {"Hair Color", type text}, {"Fav Ice Cream", type text}}),
    #"Grouped Rows" = 
        Table.Group(
            #"Changed Type", 
            {"Name", "Age", "Hair Color"}, 
            {
                {"Flavor", 
                each 
                Text.Combine(
                    List.Transform(
                        [Fav Ice Cream],
                         Text.From
                         ),
                    ", "
                    ), 
                    type text
                }
            }
        )
in
    #"Grouped Rows"

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.

[cah2035]

Hi @edhans 

Thanks, that worked for me. 

Also, how did you get that top row where is shows you the count of empties and errors? That would be extremely useful!

[edhans]

Great @cah2035 - Can you mark it as the solution so others can find it and know that it worked?

In Power Query, go to the View tab.

Check Column Distribution and Column Quality.

Column Profile is also handy, but takes up half of the screen, so I turn it on/off as needed.

[cah2035]

Hi @edhans 

It looks like I don't have those options:

[cah2035]

@edhans 

I figured it out, these options are not available in Power Query for Excel, but I do have Power BI so I see them there. I still don't fully understand when I should use Power BI instead of Power Query in Excel, but I guess this is a good example of why I should switch over. 

[edhans]

Those options do exist in Excel, but you need to have either Excel 2019, or Office 365. They have been available for a few years, but not in Excel 2016 or earlier.