Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

complex duplications

Hi, I'm looking for the way to eliminate a bit more complex duplicates. The rule would be something along those lines: If: the same job category, same date and postcode, then deduplicate entry which has lower or same calculated value (Open=1, Booked=2, Closed=3). If same deduplicate on later entry. Column with calculated value has been already created. But does anyone know how to create that deduplication query? Many thanks in Advance!

Need Help

2 Replies

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    9 years ago

    Anonymous

     

    According to your description, you want to keep the row with biggest value within category, date, and postcode group. Right?

     

    In this scenario, you just need to use SUMMARIZE to aggregate entries on category, date and postcode level.

     

    Calculated Table =
SUMMARIZE (
    Table,
    Table[category],
    Table[date],
    Table[postcode],
    "Status", MAX ( Table[Status Code] )
)

    Regards,

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi Simon,

      Sorry for a late reply.

      That part worked! But I need other columns too in the table, unless I can somhow link it back to the Orginal table, which contains Job ID, booked.

       

      Also  Is there a fast way of exportingtoa CV with such calcualted table to check if everything worked properly?

       

      Many Thanks