Two values against sales, return only 1

Chewdata, an observation, not a criticism; you could eliminate the Count column by reversing the 0/1 in the add_score step, don't create a count column in the GroupedRows step and change the if in the add_approvedpending step to read each if [Approved] > 0 then "Pending" else "Approved"

Tob_P , Here is the power query to achieve desired output

Hey!

This is how I would solve this.

1. Create score column Approved = 1, pending = 0.
2. Group columns, add columns: Count, sum of score and All rows.
3. Add new Approved/Pending column. If Score = Count then approved, else Pending.
4. Remove unnessecary columns
5. Expand the table to get all rows back.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvY3NzA3NDJV0lEKSM1LycxLV4rVQRF2LCgoyi9LTUGIm5tbYFMOEcalnO7iFsaGBlicCRXGpZxScUNDU0sjTGthwriUo4ubmJhaGJIgbm5pamGGaS2GcCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sales Order No" = _t, #"Approved/Pending" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Sales Order No", type text}, {"Approved/Pending", type text}}),
    add_score = Table.AddColumn(ChangedType, "Score", each if [#"Approved/Pending"] = "Approved" then 1 else 0, Int64.Type),
    GroupedRows = Table.Group(add_score, {"Sales Order No"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Approved", each List.Sum([Score]), type number}, {"Table", each _, type table [Sales Order No=nullable text, #"Approved/Pending"=nullable text, Score=number]}}),
    add_approvedpending = Table.AddColumn(GroupedRows, "Approved/Pending", each if [Approved] = [Count] then "Approved" else "Pending", type text),
    RemovedColumns = Table.RemoveColumns(add_approvedpending,{"Sales Order No", "Count", "Approved"}),
    ExpandedTable = Table.ExpandTableColumn(RemovedColumns, "Table", {"Sales Order No"}, {"Sales Order No"})
in
    ExpandedTable

 Hopefully this is helpfull!

Easy enough with calculated column,

Another

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvY3NzA3NDJV0lEKSM1LycxLV4rVQRF2LCgoyi9LTUGIm5tbYFMOEcalnO7iFsaGBlicCRXGpZxScUNDU0sjTGthwriUo4ubmJhaGJIgbm5pamGGaS2GcCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sales Order No" = _t, #"Approved/Pending" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Sales Order No", type text}, {"Approved/Pending", type text}}),
    GroupedRows = Table.Group(ChangedType, {"Sales Order No"}, {{"AllRows",each _[#"Sales Order No"]},{"Approved/Pending", each if List.Contains(_[#"Approved/Pending"],"Pending") then "Pending" else "Approved"}}),
    ExpandedAllRows = Table.ExpandListColumn(GroupedRows, "AllRows"),
    RemovedColumns = Table.RemoveColumns(ExpandedAllRows,{"AllRows"})
in
    RemovedColumns