Create a calculated column by creating a loop.

[Jagen007]

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) and the loop run again.

Example:

Input

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

Output

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	3

[PhilipTreacy]

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

•  

[PhilipTreacy · Accepted solution]

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]

Hi Philip, 

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