Need to build new table who have unique filtered data rows only from existing table

[AmitSaini]

Hi Community Members,

Need your help to build new table from the existing table. below is the table look like.

As you seen in the above table, we have some duplicate id's with different status and auto_status.

I want to create new table using the above table , if we have any duplicate id then we consider only id who have the status= Yes and Auto_Status = Candidate for Automation only.

Taking example for 1st id - 1047452, need to keep only second row only because who have the status= Yes and Auto_Status = Candidate for Automation, first row should remove. 

And my result table or final table look like below:- 

Please help me to achieve this in Power query or Dax.

Thanks

[v-fenling-msft · Accepted solution]

Thanks for bhanu_gautam's concern about this issue.

 
Hi, @AmitSaini 

I am glad to help you.

Perhaps you could try using the following DAX:

NewTable = 
UNION (
    FILTER (
        ExistingTable,
        ExistingTable[status] = "Yes" && ExistingTable[Auto_Status] = "Candidate for Automation"
    ),
    FILTER (
        ExistingTable,
        COUNTROWS (
            FILTER (
                ExistingTable,
                ExistingTable[ID] = EARLIER(ExistingTable[ID])
            )
        ) = 1
    )
)

Output:

I have attached the pbix file for this example below, I hope it helps!

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AmitSaini]

Hi @v-fenling-msft 

I appreciate the solution. It’s working perfectly.

It would be very helpful if you could provide the same solution in Power Query. This way, I can add only the final output table to my data model, rather than including the original table.

[bhanu_gautam]

@AmitSaini , Create a new table using DAX

NewTable =
FILTER (
ExistingTable,
ExistingTable[status] = "Yes" && ExistingTable[Auto_Status] = "Candidate for Automation"
)

[AmitSaini]

hi @bhanu_gautam 

Thanks for your reply.

But it won't work. The dax table you suggested it gives us only the id who have status] = "Yes" and [Auto_Status] = "Candidate for Automation" and exclude others rows who have unique rows entry.

for e.g. id 1188065,1188055 and all rows from project Kodiak.

Please see my Expected table output screenshot. I need unique rows entry as well.