How to create a new column, which tells if a value is uniquely contained in another column?

Question

I'm working with power query language (M) for Excel and I would like to create a new column based on a condition like:

New_column = if [Order number] is unique, then "1" else "0"

How can I check uniqueness with M code?

Order number is a column I have in my data set (where different order numbers appear many times depending on what production phase they are in), and I would like to create a new column with dummy values 1, 0 which tells me if a the order number in the current row is unique or not (as some only undergo one production phase).

Answer 1

• Add an Index column
• Group by Order Number (and select Advanced
  • Aggregate: Count
  • Aggregate: All Rows
• Add Conditional column
  • Something like if the count column = 1, 1,0
• Expand the table (all rows except for the Order Number which is already there
• Re-Sort by the Index column to get back into original order
• Delete the extraneous columns (probably Count and Index)