Loop Function

wsindharta,

sample data and assignment:

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdJLDsMgDATQu7BOJP8xZ4ly/2sUQtSaulK6AwY9RhbHUdyAsaFS2QoqoleC6n0TA7Z3WgF4puRKxjsCUCvn9i+FkZKbqqDSBtXjm5r807tb6QFAvEbf5qfeONOlgKYCFEwTb32NdRTvRyROwvtIbTEpmpZM/mF6NOtlejRF4WlSGbWI0oXqUnQZaE2m/DBbMBkvcxmotKfpZ1Njz2nK0lOi6amnZpMgmDTN/pnOFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order No." = _t, #"To Part No" = _t, #"To Container" = _t, #"To Qty" = _t, #"From Part No" = _t, #"From Container" = _t]),
    Result = 
        [
            //remove spaces and dots from column names end + added Index column
            data = Table.Buffer(Table.AddIndexColumn(Table.TransformColumnNames(Source, each Text.Trim(_, {" ", "."})) , "Index", 0, 1, Int64.Type)),
            lg =
                List.Generate(
                    ()=> [  x = 0,
                            idx = 0,
                            merge = Table.NestedJoin(Table.FromRecords({data{x}}), {"From Part No", "From Container"}, data, {"To Part No", "To Container"}, "Merge", JoinKind.LeftOuter),
                            original = [ Original Order No = data{idx}[Order No], Original To Part No = data{idx}[To Part No], Original To Container = data{idx}[To Container] ],
                            output1 = [Level = 1] & original & data{0},
                            output2 = output1
                         ],
                    each [  idx ] <= List.Max(data[Index]),
                    each [  x = [x]+1,
                            idx = if output1 = [output1] then [idx]+1 else [idx],
                            merge = Table.NestedJoin(Table.FromRecords({[output2]}), {"From Part No", "From Container"}, data, {"To Part No", "To Container"}, "Merge", JoinKind.LeftOuter),
                            original =  if Table.IsEmpty(merge{0}[Merge])
                                        then [ Original Order No = data{idx}[Order No], Original To Part No = data{idx}[To Part No], Original To Container = data{idx}[To Container] ]
                                        else [original],
                            output1 = if Table.IsEmpty(merge{0}[Merge]) then [Level = 1] & [output1] else [Level = [output2][Level]+1] & merge{0}[Merge]{0},
                            output2 = if output1 = [output1] then [Level = 1] & original & data{idx} else original & output1
                         ],
                    each [output2]
      
            ),
            toTable = Table.RemoveColumns(Table.FromRecords(lg), {"Index"}, MissingField.Ignore)
        ][toTable]
in
    Result