Populate new table field with list last value

[vstamati]

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 imposible to achieve and I think this is due to circular reference.

Thanks in advance

[vstamati]

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]

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]

@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]

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

[Jimmy801]

Hello @vstamati 

I don't know your dataset. but if you have a column with a list, you can use Table.TransformColumns to go through every item of a specific column. On the function of this column use each List.Last(_) and your list column should get the last value of your lists.

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]

Actually I'm already calculating a single value using List.Last( ) and I want that value to populate the new field ( in the same table ) for all records. If I try to invoke the custom function and create the new field I'm getting the following error
"Expression.Error: A cyclic reference was encountered during evaluation."

[Jimmy801]

Hello @vstamati 

what you mean by create new field...?

Can you share your M-code and some data? When you add a new column to calculate the last item of you list or to use Table.TransformColumns there is no way you create a cyclic reference. Probably the list contains items that create a cyclic reference.

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

[mahoneypat]

Did you try to wrap your current list output with List.Last(  )?

Regards,

Pat

[vstamati]

Correct