How can I retain the row position when removing duplicate values?

[Garrett Doll]

 In a column I have a group of duplicate values, followed by another group of duplicate values (that are different from the first group), followed by another, etc.  For example, 1-Dec 1-Dec 1-Dec 2-Dec 2-Dec 2-Dec 3-Dec 3-Dec 3-Dec. I want the first instance of the unique value to be displayed and the subsequent duplicates removed. For example, 1-Dec <empty space> <empty space> 2-Dec <empty space> <empty space> 3-Dec <empty space> <empty space>. The "Remove Duplicates" command removes the duplicates but doesn't retain their row position. Any ideas? Thanks!

[#2]

Hi Garrett Doll,

- First order the column containing the duplicate values in ascending order.

- Place a helper column with some name say "filter" beside the above column.

- Place zero in the first cell of the above helper column

- Then place the below formula in the 2nd cell of the helper column

- Drag the formula until you need it

- Apply the filter on all columns and then uncheck all values except zero in the helper column

- Delete the filtered values in the Duplicates column

- Now remove the filters from all columns, you should be left with the result that you desired for.

Thanks,

Bala..

[#3]

Hi Garrett,

If we speak about Power Query when Remove Duplicates removes entire duplicated in columns you defined rows. What you need is to replace duplicates in one of columns. One of variants is to add index columns to your table for the reference on previous row; replace in the column all data if they are the same as in previous row on what you want; remove index columns then. Something like

...
    AddIndex = Table.AddIndexColumn(PrevStep, "Index", 0, 1),
    ReplaceDuplicates = Table.ReplaceValue(AddIndex,
         each [Date],
         each if AddIndex{[Index]}[Date] = AddIndex{[Index]-1}[Date] then null else AddIndex{[Index]}[Date],
        Replacer.ReplaceValue, 
        {"Date"}),
    RemoveIndex = Table.RemoveColumns(ReplaceDuplicates,{"Index"})
...

Result is like this

and please see attached.