Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
vstamati
Frequent Visitor

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

 

Thanks in advance

9 REPLIES 9
vstamati
Frequent Visitor

This is my table called Cash_Flows

Data.jpg

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 

Invoke function fnZSpread.jpg

function fnZSpread result.jpg

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 :

Error.jpg

 

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

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

 

Historical Data.jpg

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
Community Champion
Community Champion

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

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."

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
Microsoft Employee
Microsoft Employee

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

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Correct

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors