Forum Discussion

Garrett Doll's avatar
Garrett Doll
Copper Contributor
Dec 28, 2017

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

 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-D...
BI & Data Analytics
Need Help
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Dec 28, 2017

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.

Resources