Power Query Mcode to add custom columns based on values in another column

[sadavies2906]

I currently have the following output generated by a series of power queries using M code. I can make the changes within Excel once I output the results but would really like to get everything done within the query.

1. Need to get a column that auto generated a sequential number based on the value within the Order column that increment when it come across a new value.
2. When the incremented number is found more than once to populate a second column with the value "SHORTAGE" else populate with "SHORTAGE FREE"

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],  //Change Table2 to your table name
    Group = Table.Group(Source, {"Order"}, {{"x", each Table.AddColumn(_, "Shortage Status", (x)=> (if Table.RowCount(_)=1 then "Shortage Free" else "Shortage"))}}),
    AddIndex = Table.AddIndexColumn(Group, "Seq", 1, 1, Int64.Type),
    Expand = Table.ExpandTableColumn(AddIndex, "x", {"Shortage Status", "Material Type"}),
    Reorder = Table.ReorderColumns(Expand,{"Seq", "Order", "Shortage Status", "Material Type"})
in
    Reorder