Forum Discussion
How to calculate column by groups in Power Query
HI!
I am using this code to calculate my previous value from calculated column /(1+Rate) in Power Query
Deflator = List.Transform(List.Skip(List.Accumulate(#"Changed Type3"[Rate], {1}, (s,c)=> s & {List.Last(s)/(1+c)})), Number.From),
Vlastné2= Table.FromColumns( Table.ToColumns(#"Changed Type3")& {Deflator}, Table.ColumnNames(#"Changed Type3")& {"Deflator"}),
I need to cacluĺate by groups so when there is different group it should start again with 1.
It should work like this
Group Value
A B2=1/(1+A2)
A B3=B2/(1+A3)
A B4=B3/(1+A4)
B B5=1/(1+A5)
B B6=B5/(1+A6)
B B7=B6/(1+A7)
Is there some way to calculate this by groups ?
Thank you !
NewStep=Table.FromRecords(List.Accumulate(Table.ToRecords(#"Changed Type3"),{{},[]},(x,y)=>let a=Record.TransformFields(x{1},{y[Group],each (_??1)/(1+y[Rate])},2) in {x{0}&{y&[Deflator=Record.Field(a,y[Group])]},a}){0})
5 Replies
NewStep=Table.FromRecords(List.Accumulate(Table.ToRecords(#"Changed Type3"),{{},[]},(x,y)=>let a=Record.TransformFields(x{1},{y[Group],each (_??1)/(1+y[Rate])},2) in {x{0}&{y&[Deflator=Record.Field(a,y[Group])]},a}){0})
wdx223_Daniel It works in Power Query but when I wanna use it in PBI there is error : Expression.Error: Evaluation resulted in a stack overflow and cannot continue.
Is there some way to fix it ?
your real data is too big to use this solution
you can try to group the data firstly
wdx223_Daniel Thank you !