Forum Discussion

CoreX's avatar
CoreX
Regular Visitor
9 years ago
Solved

Remove duplicate rows based on max value of a different column

Hi All,   I'm attempting to filter some of my data and I'm having problems getting it correct.  Through SQL I'm pulling some sales data in to a table.  Each sales order has it's own unique number (...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    Hi CoreX,

     

    You want all the rows. So we need to create a new table. There are two options. Please have a try.

    Option 1: (Data source: Sales_Option1)

    1. Add one column to the table, which will be the Max version.

    LatestVersion =
CALCULATE (
    MAX ( 'Sales_Option1'[VERSION] ),
    ALLEXCEPT ( Sales_Option1, Sales_Option1[SALES ORDER NUMBER] )
)

    2. Create a new table.

     

    Result_Option1 =
FILTER ( Sales_Option1, Sales_Option1[VERSION] = Sales_Option1[LatestVersion] )

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Option 2: (Data source: Sales_Option2)

    One step:

     

    Result_Option2 =
FILTER (
    ADDCOLUMNS (
        Sales_Option2,
        "latestV", CALCULATE (
            MAX ( Sales_Option2[VERSION] ),
            ALLEXCEPT ( Sales_Option2, Sales_Option2[SALES ORDER NUMBER] )
        )
    ),
    Sales_Option2[VERSION] = [latestV]
)

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi CoreX,

 

You want all the rows. So we need to create a new table. There are two options. Please have a try.

Option 1: (Data source: Sales_Option1)

1. Add one column to the table, which will be the Max version.

LatestVersion =
CALCULATE (
    MAX ( 'Sales_Option1'[VERSION] ),
    ALLEXCEPT ( Sales_Option1, Sales_Option1[SALES ORDER NUMBER] )
)

2. Create a new table.

 

Result_Option1 =
FILTER ( Sales_Option1, Sales_Option1[VERSION] = Sales_Option1[LatestVersion] )

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Option 2: (Data source: Sales_Option2)

One step:

 

Result_Option2 =
FILTER (
    ADDCOLUMNS (
        Sales_Option2,
        "latestV", CALCULATE (
            MAX ( Sales_Option2[VERSION] ),
            ALLEXCEPT ( Sales_Option2, Sales_Option2[SALES ORDER NUMBER] )
        )
    ),
    Sales_Option2[VERSION] = [latestV]
)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

heimk008's avatar
heimk008
Frequent Visitor
8 years ago

Hey v-jiascu-msft,

 

Do you have another way of doing this withing the query editor? I have a set of data that I am trying to remove duplicates based on the number of filled cells per row and I was able to get a number (like the version number) for each of my rows which will give me a max. But, once I get rid of the duplicates I want to be able to append this query with other ones, so I need it de-duped within the query editor. 

 

Any way you could help me with that? 

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi heimk008,

     

    I would suggest you create a new thread in this forum to discuss your needs that is different from this one.

     

    Best Regards,

    Dale