Forum Discussion

wsindharta's avatar
wsindharta
Helper I
2 years ago
Solved

Loop Function

Hi,    i need help, i have one table and need loop function.  below is the table, basically after i select the desired order no,  i will get "To Part No", "To Container" "To Qty", "From Part No", ...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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
dufoq3's avatar
dufoq3
Community Champion
2 years ago

Hi wsindharta, set your sample data as public please.

wsindharta's avatar
wsindharta
Helper I
2 years ago

Done

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi wsindharta,

    I'm sorry but I don't get the logic.

     

    Could you explain this?

     

    Describe it from the beginning in google drive sample sheet please, because sample data doesn't match with what you posted here as 1st post. Thank you.

    • wsindharta's avatar
      wsindharta
      Helper I
      2 years ago

      hi dufoq3 

       

      sorry for late reply, i was away for CNY holiday, have no access to my computer :D. i have remove the other information on sample data so now it match my first post, and edit the explanation. hope this is clearer. 

       

      thanks 

      • dufoq3's avatar
        dufoq3
        Community Champion
        2 years ago

        Hi wsindharta,

         

        I was away for holiday too. Solution below, but for future request - be more precise with sample data!! There is a mistake again...

         

        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 = 
        [
            data = Table.Buffer(Table.AddIndexColumn(Source, "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),
                            output1 = [Level = 1] & 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),
                            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] & data{idx} else output1
                         ],
                    each [output2]
      
            ),
            toTable = Table.RemoveColumns(Table.FromRecords(lg), {"Index"})
        ][toTable]
in
    Result