How can I refer a column value from previous row of the same column in Power Query?

[Mp1977]

Hi !

I need to perform calculations in Power query which I already did in excel sheet. In one of the formulas I'm referring to cell just above it. For instance, cell F45 = cell A1 + cell F44.

I tired to follow examples using an index field But the solution doesn't work if the referring column and the output column in same as in my case.

Any thoughts?

[v-alq-msft]

Hi, @Mp1977 

I wonder if you want to create a meausre or a calculated column or a custom column. The ways of calculation differ from each other. The value of a calculated column is computed during data refresh and uses the current row as a context; it does not depend on user activity on the pivot table. A measure operates on aggregations of data defined by the current context. While a custom column is generated when you run a query in the 'Query Editor'. 

Could you please show us some sample data and expected result with OneDrive for business? Do mask sensitive data before uploading. Thanks.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

a brutal translation of the excel formula into power query could be as follows.
But it would be preferable, in my opinion, to look for a solution to the problem using the features and functions of PQ rather than simulating excel.
If you expose more broadly what the context is and what the goal is, we could look for a power query like way

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcYxDQAACAMwL7t52EANwb8NFnp1BolAJjYGdMmvXOlbbtW33W7sHg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [A = _t, F = _t]),
    tab = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"F", Int64.Type}}),
    #"Added Conditional Column" = Table.TransformColumns(tab, {"F", each if List.PositionOf(tab[F],_)=44  then tab[F]{43}+tab[A]{1} else _})
in
    #"Added Conditional Column"

if instead I wanted to use the formula iteratively on the whole column F and if the values of F are all distinct, you could use this code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcYxDQAACAMwL7t52EANwb8NFnp1BolAJjYGdMmvXOlbbtW33W7sHg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [A = _t, F = _t]),
    tab = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"F", Int64.Type}}),
    #"Added Conditional Column" = Table.TransformColumns(tab, {"F", each  try tab[F]{List.PositionOf(tab[F],_)-1}+tab[A]{0} otherwise _} )
in
    #"Added Conditional Column"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcYxDQAACAMwL7t52EANwb8NFnp1BolAJjYGdMmvXOlbbtW33W7sHg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [A = _t, F = _t]),
    tab = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"F", Int64.Type}}),
    #"Added Conditional Column" = Table.TransformColumns(tab, {"F", each  try tab[F]{List.PositionOf(tab[F],_)-1}+tab[A]{0} otherwise _} )
in
    #"Added Conditional Column"

Otherwise with the addcolumn function and the help of an index column

[Mp1977]

@Anonymous , @v-alq-msft ,

Thank you for your reply. It is the scenario I have in more detail with a example. I need to generate a cummulative form a operation. I guess I can handle the first total row, using a if statment.

I'd rather do it on PQ then Dax.

Any suggestions?

Interest	Value	Total	Total
0,015	500	507,5	=B2*(1+A2)
0,0325	500	1040,24375	=(B3+C2)*(1+A3)
0,025	500	1578,74984375	=(B4+C3)*(1+A4)
0,015	500	2109,93109140625	=(B5+C4)*(1+A5)

[artemus]

Here is a compact solution (in Add custom column):

= Function.ScalarVector(type function (x as [Interest = number, Value = number]) as list, (z) => let _ = PreviousStep in List.Accumulate({0 .. Table.RowCount(_) - 2}, {[Value]{0} * (1 + [Interest]{0})}, (prev, i) => prev & {([Value]{i + 1} + List.Last(prev)) * (1 + [Interest]{i + 1}?)}))(_)

Replace PreviousStep with the name of the step before the custom column.

[Anonymous]

or try this (since you used the term cumulative)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcYxDQAACAMwL7t52EANwb8NFnp1BolAJjYGdMmvXOlbbtW33W7sHg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [A = _t, B = _t]),
    tab = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}}),



colC = List.Accumulate({1..Table.RowCount(tab)-1},{tab[B]{0}*(1+tab[A]{0})},(s,c)=> s&{(s{c-1}+tab[B]{c})*(1+tab[A]{c})}),

tabC= Table.FromColumns(Table.ToColumns(tab)&{colC}, {"A","B","C"})

in
    tabC

[Mp1977]

@Anonymous ,

Thank you very much !

I have not a single idea on how did works, but it does !

I don't want to abuse, but do you mind to clarify me  this part 

(s,c)=> s&{(s{c-1}+tab[B]{c})*(1+tab[A]{c})}),

[Anonymous]

@Mp1977 

I try. But in addition to my difficulties with English, there may be some substantial inaccuracies, but I hope it gives at least the idea.

(s,c)=> s&{(s{c-1}+tab[B]{c})*(1+tab[A]{c})}),
This part of the expression is the third parameter of the list.accumulate function.
it is, in turn, a function that takes two parameters as input and returns "something" that depends on these parameters.
The two input parameters are

• the "something" that the function returns and updates at each step (parameter s, status)
• and the counter (parameter c, current) which at each step assumes the value of the successive elements of the list which is the first parameter ({ 1,2, ..., number of table rows}) of the list.accumulate function.

The second aprameter of list.accumulate is the initial value of s and is used in the first step of the iteration.

So to go in the specific case,  the initial value osf s used in to the first step is
s_0 = {tab [B] {0} * (1 + tab [A] {0})} = {11 * (1 + 1)} = {22} which is a list of only one element; while c = 1.

So the first step of the function
(s, c) => s & {(s {c-1} + tab [B] {c}) * (1 + tab [A] {c})}),
equals {22} & {(s {0} +22) * (1 + 22))} = {22.132}

and so on until c = number of lines -1

[Mp1977]

Thank you very much for use your time to explain me.

[Anonymous · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcYxDQAACAMwL7t52EANwb8NFnp1BolAJjYGdMmvXOlbbtW33W7sHg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [A = _t, B = _t]),
    tab = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}}),



colC = List.Generate(()=>[C=tab[B]{0}*(1+tab[A]{0}), i=0], each [i]<Table.RowCount(tab), each [C=([C]+tab[B]{i})*(1+tab[A]{i}), i=[i]+1], each [C]),

tabC= Table.FromColumns(Table.ToColumns(tab)&{colC}, {"A","B","C"})

in
    tabC

try this

[mahoneypat]

You will need to add it as a custom column in query.  Or I think it would be even easier to do running totals on the DAX side with a measure.  Calculations are different than in Excel.  While initially confusing, it is worth learning as it opens up much more possibilities for analysis.  You can do running totals using a Quick Measure, or you can do it with DAX with this approach - https://www.sqlbi.com/articles/computing-running-totals-in-dax/

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat