Loop Function

[wsindharta]

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", "From Container". then i need to loop by matching "From container" with "To container" and "From Part No" with "To Part No", and find the next "From container" and "From Part No", continue looping until no more matching. 

is there any way to do this in power query ? 

thanks 

regards,

Sind

[wsindharta]

Hi @dufoq3 

thanks for looking into this issue, i attached the sample. 

after i thought, it is possible to do it with DAX ? because the row data has 1M row +, i think it will take forever to do looping for each row in power Query. 

Tracebility sample file 

hope you can help me. 

thanks

[dufoq3]

Hi @wsindharta, set your sample data as public please.

[wsindharta]

Done

[dufoq3]

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]

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]

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

[wsindharta]

Hi @dufoq3 

sorry with some data error, i am not diligent enough to notice the issue. i need one more help, my mistake that i didnt careful enough to request in the first place, but i need a column to tied all loop result with original 1st level data. i have change the file in share drive for desired output. 

[dufoq3 · Accepted solution]

@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

[wsindharta]

hi @dufoq3 

thank you very much ! you are amazing !!

one more questions if you can share, where can i learn the "advanced" dax/ power query ? i want to up my level too...

thanks

regards

Sind

[dufoq3]

Hi @wsindharta, you're welcome. I started to learn power query with Leila Gharani course. Then I watch a lot of youtube videos + read 1 power query book via Michal Chmelar (but it is in Slovak language). The key is to practice everything you've learned. Great resource is Gorilla.Bi by Rick de Groot. If I have to check some M function I use PowerQuery.How the most (by Rick de Groot also).

[wsindharta]

hi @dufoq3 

thanks. sorry but one more thing is it possible to do this transformation in DAX ? i just realized how long it takes when i try to applied to actual data set. data set it self has 1.2Million row, and so far it already took 1 hour and only finish load 123 rows. 

what i have in mind, if we do it in DAX, the user will input Part No. and it will only loop that particular Order and Part No. 

is it possible ? 

[dufoq3]

Sorry @wsindharta, but I'm not that skilled in Dax.

[wsindharta]

Hi @dufoq3 

thanks, let me ask other who view this thread, hopefully someone can help with DAX.

Hi anyone can help me to create similar solution in dax ? 

thanks

regards,

Sind

[dufoq3]

Hi @wsindharta, provide sample data as table so we can copy it. Also provide expected result based on sample data please.