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
Mp1977
Helper II
Helper II

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

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?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

 

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

View solution in original post

10 REPLIES 10
v-alq-msft
Community Support
Community Support

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
Not applicable

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

 

 

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

InterestValueTotalTotal
0,015500507,5=B2*(1+A2)
0,03255001040,24375=(B3+C2)*(1+A3)
0,0255001578,74984375=(B4+C3)*(1+A4)
0,0155002109,93109140625=(B5+C4)*(1+A5)
artemus
Microsoft Employee
Microsoft Employee

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
Not applicable

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

 

@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
Not applicable

@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

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

Anonymous
Not applicable

 

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

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





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


Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors