Conditionally remove duplicates in one column

[Opal55]

Not sure how to do this.  I have one column in a table that has duplicate values that I want to remove in the Power Query Editor, but there are some duplicates that I need to keep.   The column contains work order numbers and is downloaded out of SAP.  I want to remove all of the duplicate work orders, BUT, some records have a "#" assigned as the work order number and those will be duplicated as well and I don't want to remove those.  All I have been reading is how to remove duplicates based on values in other columns, but that is not the case here.  Any suggestions?  Thank you.

[v-shex-msft]

Hi @Opal55,

Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[Opal55]

@mattww suggestion worked!  Thank you all!

[Opal55]

Sorry... I am confused by your suggestion.  If I filter out the the Work Orders that do not have a "#" and then remove the duplicates, all of the duplicated records containing a "#" are removed as well...  I just tried it.

[mattww · Accepted solution]

Sorry if I didn't explain it well

So you would split your query.

Query A = All of your data

Query B = Reference Query A, filter to only include those starting with #

Query C = Reference Query A, filter to only include those not starting with #, remove duplicates

Query D = Append Query B and Query C

The above could all be done in one query via the Advanced Editor, but if you prefer to do it via the GUI, just separate out the queries and to avoid them cluttering up your model, set queries A, B and C to not load (right click the query and untick Enable load)

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Matt

[mattww]

You could filter your table based on having an work order number that does not start with #. Remove the duplicates

Separately, filter the same table where work order does start with #

Then append the two together

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Matt