The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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 |
Solved! Go to Solution.
= #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})
= #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})