Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
wsindharta
Frequent Visitor

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", "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. 

 

wsindharta_0-1707188401228.png

 

is there any way to do this in power query ? 

 

thanks 

 

regards,

Sind

 

 

1 ACCEPTED SOLUTION

@wsindharta,

 

sample data and assignment:

dufoq3_0-1708876164828.png

 

Result:

dufoq3_1-1708876248427.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

14 REPLIES 14
wsindharta
Frequent Visitor

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

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


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Done

Hi @wsindharta,

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

 

Could you explain this?

dufoq3_0-1707377023302.png

 

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.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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 

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...

dufoq3_1-1708617601664.png

 

Result:

dufoq3_0-1708617321907.png

 

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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. 

@wsindharta,

 

sample data and assignment:

dufoq3_0-1708876164828.png

 

Result:

dufoq3_1-1708876248427.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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

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).


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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 ? 

 

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


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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
Super User
Super User

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


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors