Forum Discussion

Jagen007's avatar
Jagen007
Regular Visitor
2 years ago
Solved

Create a calculated column by creating a loop.

Given Maximum Qty in a pallet is 20.   I would like to created a loop in power query where by when each row is added downward and when the sum qty is more than 20 , the rows should be group (Pallet...
Tips and Tricks
  • PhilipTreacy's avatar
    PhilipTreacy
    2 years ago

    Hi Jagen007 

     

    Download example file with the calculations shown below

     

    Based on what you've described : when the sum qty is more than 20 the rows should be given the same pallet number.

    Based on that, the table would look like this

     

    Material                              Qty                        Pallet                   
    ABC-123 10 1
    ABC-124 5 1
    ABC-125 6 2
    ABC-126 4 2
    ABC-127 10 2
    ABC-128 13 3
    ABC-129 15 4

     

    To do this you can add an Index column, starting at 1, and use this to create the column

     

    = Number.RoundUp(List.Sum(List.FirstN(#"Added Index"[Qty], [Index]))/20)

     

     

     

    Regards

     

    Phil

PhilipTreacy's avatar
PhilipTreacy
Super User
2 years ago

Hi Jagen007 

 

Download example file with the calculations shown below

 

Based on what you've described : when the sum qty is more than 20 the rows should be given the same pallet number.

Based on that, the table would look like this

 

Material                              Qty                        Pallet                   
ABC-123 10 1
ABC-124 5 1
ABC-125 6 2
ABC-126 4 2
ABC-127 10 2
ABC-128 13 3
ABC-129 15 4

 

To do this you can add an Index column, starting at 1, and use this to create the column

 

= Number.RoundUp(List.Sum(List.FirstN(#"Added Index"[Qty], [Index]))/20)

 

 

 

Regards

 

Phil

Jagen007's avatar
Jagen007
Regular Visitor
2 years ago

Hi Philip, 

 

thank you very much for giving the solution as it working but can you please explain the formula/step ?