Forum Discussion
In Power Query Editor, how to remove the duplicates while also removing the original
Hi all, I am now at Power Bi's Power Query Editor, I like to delete all the rows that I marked red, they are duplicates, if I do right click and remove duplicated, one of the duplicates ...
For that, you need to do an extra thing.
In step 1 of grouping, go to Advance section and add an aggregation to get all rows. Something like this:
2) Then filter out the rows having Duplicates count as 1 as below:
3) Then expand other columns as below:
The end output will give you all your required columns.
You can do it in Power Query.
Here are the steps:
1) To identify the duplicate columns. Do a grouping based on the identifier. Something like this:
In your case, 'Identifier' will be there instead of 'Passenger Name'
Corresponding M Query:
= Table.Group(#"Changed Type", {"Identifier"}, {{"Duplicates", each Table.RowCount(_), Int64.Type}})
2) Delete the rows that are duplicates along with the original records.Corresponding M query:
= Table.SelectRows(#"Grouped Rows",each _[Duplicates] =1)The end result will be the only rows containing unique values in identifier section.
See if this works for you