Data validation

[abatahir]

I have sale order data, where the status is updated by the operations, I want to verify the step being followed on that order. if the step is missed or an early step is applied late a column shows error.
like:
an order A
Step 1 = Confirmed
Step 2 = Shipped

Step 3 = Delivered 
if customer return the product 

Step 4 = Returned. 

sometimes operations apply the Returned before delivered of shipped after delivered.  I want a check if the step missed. 

[Vijay_A_Verma · Accepted solution]

Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)

Sequence of steps is controlled by a list in below code - {"Confirmed","Shipped","Delivered","Returned","Cancelled"}

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXLOz0vLLMpNTVGK1YGIBGdkFhQg8V1SczLLUouQRIJSS0qL8qACThiGOGFTgmqKM7oSZwxTnLFqck7MS07NycFljAuGMS4Yxrgg+zEWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order No." = _t, Step = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Order No."}, {{"Temp", each _, type table [#"Order No."=nullable text, Step=nullable text]}}),
    fxProcessNext=(Tbl)=>
    let
        #"Added Index" = Table.AddIndexColumn(Tbl, "Index", 0, 1, Int64.Type),
        #"Added Custom" = Table.AddColumn(#"Added Index", "ErrorStatus", each if List.PositionOf({"Confirmed","Shipped","Delivered","Returned","Cancelled"},[Step])>[Index] then "Error" else null),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
    in
        #"Removed Columns",
    #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "fxProcessNext", each fxProcessNext([Temp])),
    #"Expanded fxProcessNext" = Table.ExpandTableColumn(#"Invoked Custom Function", "fxProcessNext", {"Step", "ErrorStatus"}, {"Step", "ErrorStatus"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded fxProcessNext",{"Temp"})
in
    #"Removed Columns"