Forum Discussion

vstamati's avatar
vstamati
Frequent Visitor
5 years ago

Populate new table field with list last value

I've created a custom function returning a list of values by manipulating several fields from a table. How can I populate a new field into the same table with the list's last value So far it was im...
Need Help
vstamati's avatar
vstamati
Frequent Visitor
5 years ago

Jimmy801  this is a great remark. The result is not the expected one,  because function #3 needs a small adjustment in order to work right.

 

(Cash_Flows as table, optional Lowest, optional Highest)  =>

let

Lowest = if Lowest = null then -1 else Lowest,
Highest = if Highest = null then 1000000 else Highest,

XIRRList = List.Generate( 
    
    
    ()=> [Cash_flows=Table.Buffer(Cash_Flows), Lowest=Lowest, Highest=Highest, Result=1, Counter=0, zSpread=Lowest, DiffToPrevious=0.1],

// Conditions determining if the next execution of the loop shall be performed

                    each Number.Abs([Result])>=0.000000000001 and [Counter]<1000 and [DiffToPrevious]<>0,
// loop command
                    each [                            
// Binary-search procedure
                            Lowest = if [Result]>0 then [zSpread] else [Lowest],
                            Highest= if [Result]<0 then [zSpread] else [Highest],
                            zSpread = (Lowest+ Highest)/2,  

// Goal (formulated in a way that it should be null):

                            Result= fnRunningSum(fnNPV(Cash_Flows, zSpread),"NPV") - Table.Last(Cash_Flows)[Price] ,

// Check if the result is still improving/changing

                            DiffToPrevious = Result-[Result],

                            Counter=[Counter]+1
                         ]
)
,
    Custom = List.Last(XIRRList)[zSpread]

//    record=Record.AddField([],"Spread",List.Last(XIRRList)[zSpread])


in   Custom

 

 

So far so good. The next step is to be able to perform the same calculation for my historical data , which means first group data by  BusinessDate , ISIN and PORT and then apply function #3 on every singe table within "SubTables" . Any ideas are welcome.

 

Jimmy801's avatar
Jimmy801
Community Champion
5 years ago

Hello vstamati 

 

we are here shifting from one to another request. From List to scalar value, to cyclic reference .

As I don't know your exact requirement, don't know the scenarios I can't help here without putting myself into that deep. Add a column where you are invoking your custom function, supplying the grouped table (you could also apply this function in the group-function as well). You are the business expert, so you have to know what your function has to do.

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy