Recursion problem

[shuyin]

hi,

I am pretty new PQ, and found it an indispensable tool for any Excel user. I am facing with a problem that we can easily resolve in Excel, but haven't found the way to achieve the result in PQ yet.
I want to perform recursive calculation for a column:

Input
8
6
5
6
4
8
9

--> to achieve:

	A		B
1	Input		Result
2	8		5
3	6		5
4	5		5
5	6		4
6	4		4
7	8		8
8	9		9

function in excel is B8= A8; B7= IF(A7<=A8,A7,B8), etc...

I found Chris's blog about recursion with list.generate(), but I couldn't understand fully to apply this case.
Could you help to show steps to achieve the result in PQ?

Thanks

 

[Owen Auger]

Hi shuyin,

Here is a sample of how it can be done with a list:

let SourceList = {8,6,5,6,4,8,9},

OutputListReverse = List.Generate(
  ()=>[Position=List.Count(SourceList)-1, Value = List.Last(SourceList)],
  each [Position] >=0,
  each [Position = [Position]-1, Value = if SourceList{[Position]-1} < SourceList{[Position]} then SourceList{[Position]-1} else[Value]],
  each [Value]
),

OutputList = List.Reverse(OutputListReverse)

in
    OutputList

 

[Bill Szysz]

Hi shuyin,
This can be done without recursion.
If you don't feel good in M language, you can try the method in attachement.
Only "Added Custom" step has been written by hand but this is simple if... then... else... formula.
The rest is directly from UI.

Regards

 

[shuyin]

hi,

@Bill: very smart way of using Index to match different row number to other. This approach can be applied to many different problems.

@Owen: thanks, it helps me understand better about List.generate

Do you know which way would be better in performance perspective?

Thank you both for great answers!

Regards,