Forum Discussion
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) 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 |
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
3 Replies
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
Hi Philip,
thank you very much for giving the solution as it working but can you please explain the formula/step ?
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
