Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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?
Solved! Go to 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
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.
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?
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) |
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.
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})}),
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 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.
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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
29 | |
17 | |
11 | |
9 | |
8 |
User | Count |
---|---|
42 | |
24 | |
21 | |
13 | |
12 |