How to delete some but not all duplicates

[TD21]

In my dataset, I have a "Request_ID" column. For some records, this value is set to "TBD" when the Request ID is unavailable.

What I would like to do is delete duplicate Request IDs while keeping all the "TBD"s. I can't use Delete Duplicate Rows in PowerQuery because it will delete the "TBD"s as well. How do I delete duplicate Request IDs while keeping the "TBD" values?

Would like to delete duplicates (in red) but keep the TBDs.

Request_ID

MS012345

MS662234

MT789458

TBD

MA640001

MA640001

MC987453

MK239800

TBD

MK239800

[FarhanAhmed]

What is the data source ? Can't it be done at Datasource level ?

Some sample data along with output will be appreciated.

[TD21]

@FarhanAhmed

Can't edit datasource as we don't own it (was granted SELECT only).

I updated the original post to include sample data. Thanks.

[FarhanAhmed]

Try this

let
    Source = Excel.Workbook(File.Contents("D:\BI\Power BI\Community\Book1.xlsx"), null, true),
    RequestID_Table = Source{[Item="RequestID",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(RequestID_Table,{{"RequestID", type text}}),
    #"Filtered RowsTBD" = Table.SelectRows(#"Changed Type", each ([RequestID] = "TBD")),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type"),
    #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each ([RequestID] <> "TBD")),
    #"Appended Query" = Table.Combine({#"Filtered Rows", #"Filtered RowsTBD"})
in
    #"Appended Query"

Follow the simple steps to remove duplicates and filter the table to exclude "TBD"

Then you need to add 2 things in your Power Query....

Add "#"Filtered RowsTBD" = Table.SelectRows(#"Changed Type", each ([RequestID] = "TBD"))," to select all rows of TBD just before removing duplicates

After filtering out your data you need to append this data with your filtered removed duplicates rows to get all "TBD" and distinct other values.

Hope you understand the logic

If this helps kindly mark it as "Answer" and give Kudos.

[amitchandak]

@TD21 , refer

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=a1cfd615-...

https://stackoverflow.com/questions/40176357/conditionally-deleting-rows-in-power-bi

Or move the rows you want to keep to another table delete duplicate and append the tables again.

https://radacad.com/append-vs-merge-in-power-bi-and-power-query