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 

 

The code does this:

 

  • Use List.FirstN to get the values in Qty column up to that row (using the value of the Index column on that row to sum to that row)
  • List.Sum sums these values
  • Divide by 20
  • The resultant number is round up to the nearest whole numer - this is the result

 

Regards

 

Phil

  •