Forum Discussion
Remove Duplicate Rows based on column and keep row data
I am using a dataflow and want to achieve the following in power query m.
I have a table with columns as shown below. The entity_number column repeats in some rows. If this occurs, I want to keep the row where the parent_entity_number is not null (has data). While doing this, I also want to keep all the rest of the columns for that row.
I tried grouping but have only found how to keep the 2 rows I am working with.
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
- the highest entity_number should be kept
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
entity_number entity_name parent_entity_number parent_entity_name email phone 100 school1 200 school1D1 [email protected] 000-123-1231 100 school1 null [email protected] 000-123-1232 100 school1 null [email protected] 000-123-1232 105 school2 null [email protected] 000-123-1232 105 school2 300 school1D2 [email protected] 000-123-1232 105 school2 null [email protected] 000-123-1232 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 HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
7 Replies
If you filter the parent_entity_number column to not include null values does that give you your desired result?
The code would be
Table.SelectRows(tableName, each [parent_entity_number] <> null)No, I would still want to keep the row even if it does not include a parent_entity_number.
Sort the table based on parent_entity_number in ASSENDING, BY DOING THIS THE FORMULA AS Table.Sort(...) is created, add Table.Buffer to it and rewrite it as Table.Buffer(Table.Sort(.....)) then in the next step, right click on entity_number and pick remove duplicate.
Sorry if i left out this part to add some context.
If a row has entity_number 123 but no parent_entity_number, and it is not duplicated so only 1 row with no parent, then i still would like to keep that row as well.
I think what you mean is: if there are multiple identical entity_numbers, keep the row with "parent_entity_number<>null"; if there is only one entity_number, keep the row directly.
If this is the case, you can do it as follows:
1. Group the entity_number column
2. Determine whether the table in the grouping column has only one row. If there is only one row, keep it, otherwise select only the row with "parent_entity_number<>null"
3. Delete other rows outside the grouping row
4. Expand the grouping column
The code for the grouping part is similar to the following:
Table.Group( Your_Table, "entity_number", { "Group", each if Table.RowCount(_) = 1 then _ else Table.SelectRows(_, each [parententity_number] <> null) } )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
- the highest entity_number should be kept
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
entity_number entity_name parent_entity_number parent_entity_name email phone 100 school1 200 school1D1 [email protected] 000-123-1231 100 school1 null [email protected] 000-123-1232 100 school1 null [email protected] 000-123-1232 105 school2 null [email protected] 000-123-1232 105 school2 300 school1D2 [email protected] 000-123-1232 105 school2 null [email protected] 000-123-1232 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 HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
