Forum Discussion
Solved
Keep above value if difference below threshold
I want Power Query to generate the output column based on the input column and a threshold constant (=0.9 in below example). The first output value is the first input value and then output value is either the above output value, if input is a higher value than the above output value multiplied with the threshold constant (0.9), or the corresponding input value.
| Input | Output |
| 2.00 | 2.00 |
| 1.98 | 2.00 |
| 1.96 | 2.00 |
| 1.94 | 2.00 |
| 1.92 | 2.00 |
| 1.90 | 2.00 |
| 1.88 | 2.00 |
| 1.86 | 2.00 |
| 1.85 | 2.00 |
| 1.83 | 2.00 |
| 1.81 | 2.00 |
| 1.79 | 1.79 |
| 1.77 | 1.79 |
| 1.76 | 1.79 |
| 1.74 | 1.79 |
| 1.72 | 1.79 |
| 1.70 | 1.79 |
| 1.69 | 1.79 |
| 1.67 | 1.79 |
| 1.65 | 1.79 |
| 1.64 | 1.79 |
| 1.62 | 1.79 |
| 1.60 | 1.60 |
| 1.59 | 1.60 |
= #table({"Input","Output"},List.Accumulate(PreviousStepName[Input],{},(x,y)=>if x={} then {{{y,y}},y} else if y<=x{1}*0.9 then {x{0}&{{y,y}},y} else {x{0}&{{y,x{1}}},x{1}}){0})
1 Reply
= #table({"Input","Output"},List.Accumulate(PreviousStepName[Input],{},(x,y)=>if x={} then {{{y,y}},y} else if y<=x{1}*0.9 then {x{0}&{{y,y}},y} else {x{0}&{{y,x{1}}},x{1}}){0})