Forum Discussion
Conditionally remove duplicates in one column
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.
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
5 Replies
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
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
Hi Anonymous,
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
mattww suggestion worked! Thank you all!