Remove Duplicate Rows based on column and keep row data

Starting with this:

Using this code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"entity_number", Int64.Type}, {"entity_name", type text}, {"parent_entity_number", Int64.Type}, {"parent_entity_name", type text}, {"email", type text}, {"phone", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Is Final Row", each Table.RowCount(Table.SelectRows(#"Changed Type", (row) => row[entity_number] = [entity_number] and [parent_entity_number] <> null)) > 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Is Final Row] = true))
in
    #"Filtered Rows"

Produced this:

NOTE

When there are multiple entity_number with a populated parent_entity_number, you will get them both.

In your sample data there is no way to decide which one to keep.
I could imagine that:

• the highest entity_number should be kept
  This is slightly more complicated of course, but doable.
• that the final entity_number after a chain of parents should be kept.
  Like 100 is the parenet of 200 is the parent of 150. You only want to keep 150.
  This is even more complicated and will be very slow on large volumes. But is is doable.

Did I answer your question? Then please mark my post as the solution and make it easier to find for others having a similar problem.
If I helped you, please click on the Thumbs Up to give Kudos.

Kees Stolker

A big fan of Power Query and Excel

Hi liamcal2301 ,
Thanks to all of them for their efforts.

In order to better demonstrate the implementation of the code, I added a duplicate line of data to the original data to check whether it was successful or not

Sample data

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRKk7OyM/PMQSyjJD5LoYItoGBQ25iZk5mXmJJfpFecn4uUMrAwEDX0MgYhA2VYnUwTcsrzclBUDCTjPCaZER1k0zhOoxwmmRKhknGKGFlhGSaMeXuSjE0NCRkSiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [entity_number = _t, entity_name = _t, parent_entity_number = _t, parent_entity_name = _t, email = _t, phone = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"entity_number", Int64.Type}, {"entity_name", type text}, {"parent_entity_number", Int64.Type}, {"parent_entity_name", type text}, {"email", type text}, {"phone", type text}}),
    FilteredRows = Table.SelectRows(#"Changed Type", each [parent_entity_number] <> null),
    NullParentRows = Table.SelectRows(#"Changed Type", each [parent_entity_number] = null),
    GroupedRows = Table.Group(NullParentRows, {"entity_number", "entity_name", "email", "phone"}, {{"AllData", each _, type table [entity_number=Int64.Type, entity_name=Text.Type, parent_entity_number=Int64.Type, parent_entity_name=Text.Type, email=Text.Type, phone=Text.Type]}}),
    DistinctRows = Table.TransformColumns(GroupedRows, {"AllData", each Table.Distinct(_)}),
    ExpandedRows = Table.ExpandTableColumn(DistinctRows, "AllData", { "parent_entity_number", "parent_entity_name"}),
    FinalTable = Table.Combine({FilteredRows, ExpandedRows}),
    #"Sorted Rows" = Table.Sort(FinalTable,{{"entity_number", Order.Ascending}})
in
    #"Sorted Rows"

Final output

Best regards,
Albert He

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