Forum Discussion

AndreaPiscitell's avatar
AndreaPiscitell
Frequent Visitor
6 years ago
Solved

Create new Table and eliminate duplicate based on more values/columns with Unique row ID

Hi everyone,

as usual, i am sorry this question has been asked already. 

I have a table (Table1) with a column of unique values per row (ServiceID), and then columns such as Date, Timestamp, Username and Brand.

The goal is to create a table (Table2) which eliminated the duplicate of Username and Brand, based on the latest Time. I need to keep at least one row per Username and Brand, associated, and the ServiceID is my commune value to setup relationships with other tables. I hope you can help

 

Table1:

 

ServiceIDDateTimeUsernameBrand
45614-3-20203:54:21User2Opel
43214-3-20203:54:34User2Fiat
54714-3-20203:55:05User3Toyota
96514-3-20203:55:23User3Fiat
23214-3-20206:23:11User4Toyota
45514-3-20206:23:32User4Kia
23314-3-20206:23:56User4Toyota
11114-3-20202:45:45User5Suzuki
67514-3-20202:45:52User5Suzuki
82714-3-20202:46:01User5Ford
95615-3-202015:03:10User6Volvo
34215-3-202015:03:25User6Volvo
65015-3-202015:03:41User6Volvo
40315-3-202013:02:32User7BMW
42115-3-202013:02:48User7Ford
72116-3-20209:00:17User8Renault
53116-3-202011:08:22User9Fiat
21116-3-202011:08:35User9AlfaRomeo
11616-3-202011:08:50User9AlfaRomeo
32016-3-202011:09:03User9Fiat

 

Expected Table or Table2:

ServiceIDDateTimeUsernameBrand
45614-3-20203:54:21User2Opel
43214-3-20203:54:34User2Fiat
54714-3-20203:55:05User3Toyota
96514-3-20203:55:23User3Fiat
45514-3-20206:23:32User4Kia
23314-3-20206:23:56User4Toyota
67514-3-20202:45:52User5Suzuki
82714-3-20202:46:01User5Ford
65015-3-202015:03:41User6Volvo
40315-3-202013:02:32User7BMW
42115-3-202013:02:48User7Ford
72116-3-20209:00:17User8Renault
11616-3-202011:08:50User9AlfaRomeo
32016-3-202011:09:03User9Fiat

 

Thanks, Andrea

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hey AndreaPiscitell 

     

    First, create a reference of the original query in Query Editor.

     

    Next, create a calculated column with the follow dax:

     

    Username/Brand = CONCATENATE(Table2[Username],Table2[Brand])

     

    https://docs.microsoft.com/en-us/dax/concatenate-function-dax

     

    Then you can remove duplicates in this column. If you have it sorted by date oldest to newest it should keep the old (first instance in table) and remove the older ones. Doing it this way will allow the table to update and keep the new oldest date whenever you refresh. Also, since you still have the Service ID you can just use the MAX function to find the max date for the Service ID in table 1 if necessary.

     

    If this helps please kudo.

    If this solves your problem please accept it as a solution.