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

     

     

     

konstantinos's avatar
konstantinos
Memorable Member
10 years ago

Anonymous 

 

If sales stage is a number ( or duplicate the column - convert to number ), then you can select all columns exept "Sales Stage" , and then in Transform tab "Group by" and change the new column operation to to 'max' and then choose the sales stage column.

 

That is the easy BUT might still leave you duplicate ID if you have ID had different values for another column.

 

If that is the case ( more likely ) check this post by Ken Plus 

http://www.excelguru.ca/blog/2016/05/25/keep-the-most-recent-entry/

 

 

Anonymous's avatar
Anonymous
Not applicable
10 years ago

We do have different values in another column. The post from Ken Plus assumes we are using PowerQuery. But we are using Power BI Desktop and I don't understand how to make that work in Power BI Desktop (if at ll possible).

 

Thank you,

Oscar Broekman

  • konstantinos's avatar
    konstantinos
    Memorable Member
    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
      10 years ago

      Thank you. I am able to follow all steps, until ' Click the formula icon (fx) in the bar. ' I don't see that white bar in my screen. How do I make that white bar show?

       

       

    • Emmaxson's avatar
      Emmaxson
      Advocate I
      9 years ago

      Awesome! It did work for me.  konstantinos Where did you learn that from? I need to up my game too.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Excellent! You made my day. I just had to create an Indexed column in the query editor, sort descending on Index, and then buffer.