Change status if nothing was shipped

[Usates]

I have multiple document numbers in various status’s. I need a new column titled “Revised_Status”.

When I have a document number that is in status “Closed” and where no lines have a shipped qty within the same document number, I want a new status of “Cancelled”, else [Status]. I would like to do this in query editor.
Sample Status.pbix (603.0 KB)

Thank you for your help!

[Melissa]

Hi @Usates,

Paste this into a New Blank Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZRbCoMwEEX3km8LebXR33YBXYD40dQUCqEtdP9QhZCJoTeJYHDAOdfDBR1HxsVhuaRgHeMmjuu5hft6Lv79dTObuhJg9wL3KhAezuHOcXIfR9nmvgUa3LcAcpcEDHFUBSUIICWZtqMSlbwdWhQ8jrpJJQPqKhmA2lEEULPHghIEkFICkN0pe8P56X0VsFUgLKb9NyXPrclub/IDJGsCiDWFViCAWtGpuym0ApNRKwlAY5+5Xz/uVVm37etD4auCAPpJQcD9AaYf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Ship Date" = _t, #"Document Number" = _t, Item = _t, #"Qty Ordered" = _t, #"Shipped Qty" = _t, Status = _t]),
    GroupRows = Table.Group(Source, {"Document Number", "Status"}, {{"TotalShipped", each List.Sum( List.Transform([Shipped Qty], Number.From)), type nullable number}, {"AllRows", each _, type table [Date=nullable date, Ship Date=nullable text, Document Number=nullable number, Item=nullable text, Qty Ordered=nullable number, Shipped Qty=nullable number, Status=nullable text]}}),
    AddNewStatus = Table.AddColumn(GroupRows, "Revised_Status", each if [Status] = "Closed" and [TotalShipped] =0 then "Cancelled" else [Status], type text ),
    ExpandRows = Table.ExpandTableColumn(AddNewStatus, "AllRows", {"Date", "Ship Date", "Item", "Qty Ordered", "Shipped Qty"}, {"Date", "Ship Date", "Item", "Qty Ordered", "Shipped Qty"}),
    SelectColumns = Table.RemoveColumns(ExpandRows,{"Status", "TotalShipped"})
in
    SelectColumns 

.

1. Group By Doc no, Status and Sum Total Shipped
   

   

2. Add Revised Status
   

   

3. Expand data and Remove helper columns
   

   

I hope this is helpful.