Recursion problem

shuyin

New member
Joined
Aug 11, 2016
Messages
26
Reaction score
0
Points
0
Excel Version(s)
2016
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:

AB
1InputResult
285
365
455
564
644
788
899

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
 
Hi shuyin,

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

Code:
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
 
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
 

Attachments

  • PQ_without_recursion.xlsx
    17.4 KB · Views: 16
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,
 
Back
Top