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

This is my table called Cash_Flows

Using a sequence of functions I'm replicating excel's goal seek

  • Function #1  called "fnNPV"

let
fnNPV= (Sourcetable as table, zSpread) as table =>

let
#"Added Custom" = Table.AddColumn(Sourcetable, "NPV", each Number.Exp(-zSpread/10000*[YEAR_PART])*[VALUE]*[DISCOUNT_FACTOR])
in
#"Added Custom"
in
fnNPV

 

  • Function #2 called "fnRunningSum"

(MyTable as table, MyColumn as text) =>
let
Source = Table.Buffer(MyTable),
TableType = Value.Type(Table.AddColumn(Source, "RunningSum", each null, type number)),
Cumulative = List.Skip(List.Accumulate(Table.Column(Source,MyColumn),{0},(cumulative,MyColumn) => cumulative & {List.Last(cumulative) + MyColumn})),
AddedRunningSum = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType),

CalcNPV = List.Last(AddedRunningSum[RunningSum]) / List.Last(AddedRunningSum[VALUE]) *100
in
CalcNPV

 

  • Function #3 called "fnZSpread"

(Cash_Flows as table, Price , 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") - Price ,

// Check if the result is still improving/changing

DiffToPrevious = Result-[Result],

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

in
Custom

 

Function "fnZSpread"  is returning a singe value 

What I need is to populate this value to a new field in my initial table "Cash_flows" for further processing.

So if I try to add a new step in table "Cash_flow"

#"Invoked Custom Function" = Table.AddColumn(#"Removed Columns", "fnZSpread", each fnZSpread(Cash_Flows, [Price], null, null)) , I get the following error message :

 

  • Jimmy801's avatar
    Jimmy801
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hello vstamati 

     

    you have a cycling reference in your last step. You cannot reference in the last step on the output of the query. You have to reference the previous step, not the output of the query. The last step should be like this

    #"Invoked Custom Function" = Table.AddColumn(#"Removed Columns", "fnZSpread", each fnZSpread(#"Removed Columns", [Price], null, null))


    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

    • 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
        Icon for Community Champion rankCommunity 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