Forum Discussion
In Power Query Editor, how to remove the duplicates while also removing the original
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
Thank you for your replys! but after I do the grouping and filtering, Only 2 columns left, other columns all disappear,i had more than 20 columns in this dataset. How to fix?
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 need to add each column in the grouping function, see the example are in basic, select advance and you can add more columns, add them to your grouping function.
StefanoGrimaldi
If I did what you said, I add all other columns as my "filter by". but in this case, I only want to filter by "identidier" column, I just want to use "identifier" column to filter all the rows, and keep all other columnsyou add all the columns, and created the new column as before, in advance you select in the first section all the columns, in the second section you add a new costum column using the reference column with the countrows. this will give you the amount its duplicated and them filter those bigger tham 1.
