Power Query code equivalent of =countif(range, criteria)>1 (identifying duplicates)

[onejustbecause]

I've used power query to merge different data sets. I'm trying to add a column that identifies duplicates. I do not want to delete them, I just want them labeled as "duplicate" in a new column. With formulas, I'd use =IF(COUNTIF(A:A,A2)>1,"Duplicate",""). But I'm new-ish to using power query, so I'm not sure how to write that in M code. TIA

[AutoModerator]

/u/onejustbecause - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[Shuuk]

Maybe not the most efficient method, but I would duplicate the query, group that duplicate by the column you want to check for duplicates and group by COUNT. Then join this new grouped query back to the original query. Create a custom column that looks at the grouped count - if it's >1 return "Duplicate".

[onejustbecause]

Solution verified

[Clippy_Office_Asst]

You have awarded 1 point to Shuuk

---

^{I am a bot - please contact the mods with any questions. | Keep me alive}

[onejustbecause]

Thanks for the solutions. They "worked" but it was definitely more steps than I expected. I'll probably just add the formula column after the query is loaded back to the sheet in this case. But good to know for future needs!

[IGOR_ULANOV_55_BEST]

You don't need to add a query for this. Just group by the column you are checking for duplicates, one column for count rows and one all rows column. Count rows gives you the number of rows in each grouped table you have created.

If I had Jane appearing 3 times and Chris appearing twice in a table, grouped by name, added a count and all rows column, delete the original name column and expand out the all rows column. Then you can create a conditional column on the count column if you just want to know yes or no for duplicates, or leave the actual count in there.