Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

Remove old duplicates

We have a table in Excel with CRM data. Each CRM opportunity appears multiple times. Meaning if an opportunity moves through the different CRM stages for each stage a row will be created. The CRM sta...
  • konstantinos's avatar
    konstantinos
    10 years ago

    Anonymous It is almost the same thing. The UI is a bit diffrent.

     

    In PowerBI Desktop go to  "Edit Queries" 

     

     

    Select the table/query taht you import from excel -> Select the ID column only and sort ( doesn't matter asc or desc) ->

    Select the Sales Stage column now & sort z-a ( the biggest on top )

    Notice the small numbers for the order of sorting in the column?

     

     

    now the tricky part

     Click the formula icon (fx) in the bar. This will create a new step 'Custom" and also in the bar will be the name of the previous step     = #"Sorted Rows"

     

     

    Now after equal sign wirite =Table.Buffer( #"Sorted Rows")

    SO just write the formula and include the text already there - previous step.

     

     

     

    You already know how to remove duplicates so that is.

     

    Hope it works

     

     

     

Anonymous's avatar
Anonymous
Not applicable
8 years ago

With Excel and Power Query, I had a Formula.Firewall issue when using the buffer function proposed as solution. So I found using this alternative (answer from ExcelApostle) with an index column simple and convenient:

 

There seems to be an implicit index constructed in the backend that is overwritten when you add an index column. After adding the index column, the latest sort is respected when removing duplicates. Lot faster than grouping / merging or buffering the table! Would have been nice to see this in the manual, as we cannot be sure whether after updates this implicit behavior will stay the same.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

I had the formula.fiewall issue, but it was simply because I still applied the @remove duplicates to the original table instead of #"buffered". Once corrected, it worked like a charm. I tried the Excel Apostle's solution but to no avail. It still removed new duplicates and kept the old ones. But yes, I created an indexed column in the query and sorted descending before doing the buffer step.