Forum Discussion
Capture the value of previous row in Power Query]
Hi Anonymous ,
I'm afraid it can't be achieved in Power BI, as Power Query performs operations step by step, which is different from Excel.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community SupportHi Anonymous ,
According to your description, here's my solution. Add a new step:
#"Replace Values"=Table.ReplaceValue(#"Reordered Columns",each[price],each if[Type]="DVP"then Table.SelectRows(#"Reordered Columns",(x)=>x[Serial]=[Serial]-1)[cost_per_share]{0}else[price],Replacer.ReplaceValue,{"price"})Here's the whole M syntax, you can copy-paste in a blank query.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdFNCsMgEAXgqxTXaZh5/iVZ9wCli24k979GnaeQTTUg8pAP0TelOHWLE10lrxDgIXJw1dPP9113jZZ1d+dSHAxjjNFwJPaG/S0OxOEGK7NuxJEHY+wbVuLEF/3DL+Jn14k63+neR9Mb40TD75e2iEl7/WoQq2VMGsnxqlptighjzJshDYPlTxuxEttg1MaI2R+T5VT1+QM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
#"Renamed Columns" = Table.RenameColumns(Source, {{"Column1", "Serial"}, {"Column2", "Date"}, {"Column3", "Type"}, {"Column4", "qty"}, {"Column5", "price"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"Serial", Int64.Type}, {"Date", type datetime}, {"Type", type text}, {"qty", Int64.Type}, {"price", Int64.Type}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "TA", each [qty]*[price]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Total_holdings", each List.Sum(List.Range(#"Added Custom"[qty],0,[Serial]))),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Total_cost", each List.Sum(List.Range(#"Added Custom"[TA],0,[Serial]))),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "cost_per_share", each [Total_cost]/[Total_holdings]),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom3",{{"cost_per_share", type number}, {"TA", Int64.Type}, {"Total_holdings", Int64.Type}, {"Total_cost", Int64.Type}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type2",{"Serial", "Date", "qty", "Type", "price", "TA", "Total_holdings", "Total_cost", "cost_per_share"}),
#"Replace Values"=Table.ReplaceValue(#"Reordered Columns",each[price],each if[Type]="DVP"then Table.SelectRows(#"Reordered Columns",(x)=>x[Serial]=[Serial]-1)[cost_per_share]{0}else[price],Replacer.ReplaceValue,{"price"})
in
#"Replace Values"Get the correct result:
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi v-yanjiang-msft,
Thank you so much for giving it a try however I want to replicate the same value in all the three rows like this :
So when the type is DVP the price column value should be replaced with the cost per share value. (14.47) in all the rows similarly, in the last second row, the price value should be replaced with the cost per share value (12.73) in both the rows (last row and second last row). For other types, it should be price.
Attaching the excel file where I did the manual calculations.
https://docs.google.com/spreadsheets/d/1LQzuohqY8A5KrQre_FdbeJZzh8EvMWEm/edit?usp=sharing&ouid=101701281295966826492&rtpof=true&sd=true
- v-yanjiang-msft
Hi Anonymous ,
I modify the formula as per your result.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdFNCsMgEAXgqxTXaZh5/iVZ9wCli24k979GnaeQTTUg8pAP0TelOHWLE10lrxDgIXJw1dPP9113jZZ1d+dSHAxjjNFwJPaG/S0OxOEGK7NuxJEHY+wbVuLEF/3DL+Jn14k63+neR9Mb40TD75e2iEl7/WoQq2VMGsnxqlptighjzJshDYPlTxuxEttg1MaI2R+T5VT1+QM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]), #"Renamed Columns" = Table.RenameColumns(Source, {{"Column1", "Serial"}, {"Column2", "Date"}, {"Column3", "Type"}, {"Column4", "qty"}, {"Column5", "price"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"Serial", Int64.Type}, {"Date", type datetime}, {"Type", type text}, {"qty", Int64.Type}, {"price", Int64.Type}}), #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Date", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type1", "TA", each [qty]*[price]), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Total_holdings", each List.Sum(List.Range(#"Added Custom"[qty],0,[Serial]))), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Total_cost", each List.Sum(List.Range(#"Added Custom"[TA],0,[Serial]))), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "cost_per_share", each [Total_cost]/[Total_holdings]), #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom3",{{"cost_per_share", type number}, {"TA", Int64.Type}, {"Total_holdings", Int64.Type}, {"Total_cost", Int64.Type}}), #"Reordered Columns" = Table.ReorderColumns(#"Changed Type2",{"Serial", "Date", "qty", "Type", "price", "TA", "Total_holdings", "Total_cost", "cost_per_share"}), #"Replace Values"=Table.ReplaceValue(#"Reordered Columns",each[price],each if[Type]="DVP"then Table.SelectRows(#"Reordered Columns",(x)=>x[Serial]=List.Max(Table.SelectRows(#"Reordered Columns",(x)=>x[Serial]<[Serial]and x[Type]<>"DVP")[Serial]))[cost_per_share]{0}else[price],Replacer.ReplaceValue,{"price"}) in #"Replace Values"I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi v-yanjiang-msft,
Thank you so much this is exactly what I want to show but here comes the tricky part if you see the Excel file once the price value has been replaced with cost per share so for the next calculation it should take the new price value for the calculation of cost per share. I will explain with ex:
Here, in this case, the price value has been replaced with cost per share value where type to dvp but after replacing the value the TA (price * Qty = TA )should be changed as the price is changed. Look at the highlighted values in excel you will understand. I'm wondering if this is even possible in Power BI.
https://docs.google.com/spreadsheets/d/1LQzuohqY8A5KrQre_FdbeJZzh8EvMWEm/edit?usp=sharing&ouid=101701281295966826492&rtpof=true&sd=true- v-yanjiang-msft
Hi Anonymous ,
Sorry for late back. As Power Query performs operations step by step, TA is calculated before the new price, it can't change aotumatically. We should add a new step to update its calculateion. Here's my solution:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdFNCsMgEAXgqxTXaZh5/iVZ9wCli24k979GnaeQTTUg8pAP0TelOHWLE10lrxDgIXJw1dPP9113jZZ1d+dSHAxjjNFwJPaG/S0OxOEGK7NuxJEHY+wbVuLEF/3DL+Jn14k63+neR9Mb40TD75e2iEl7/WoQq2VMGsnxqlptighjzJshDYPlTxuxEttg1MaI2R+T5VT1+QM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]), #"Renamed Columns" = Table.RenameColumns(Source, {{"Column1", "Serial"}, {"Column2", "Date"}, {"Column3", "Type"}, {"Column4", "qty"}, {"Column5", "price"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"Serial", Int64.Type}, {"Date", type datetime}, {"Type", type text}, {"qty", Int64.Type}, {"price", Int64.Type}}), #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Date", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type1", "TA", each [qty]*[price]), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Total_holdings", each List.Sum(List.Range(#"Added Custom"[qty],0,[Serial]))), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Total_cost", each List.Sum(List.Range(#"Added Custom"[TA],0,[Serial]))), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "cost_per_share", each [Total_cost]/[Total_holdings]), #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom3",{{"cost_per_share", type number}, {"TA", Int64.Type}, {"Total_holdings", Int64.Type}, {"Total_cost", Int64.Type}}), #"Reordered Columns" = Table.ReorderColumns(#"Changed Type2",{"Serial", "Date", "qty", "Type", "price", "TA", "Total_holdings", "Total_cost", "cost_per_share"}), #"Replace Values"=Table.ReplaceValue(#"Reordered Columns",each[price],each if[Type]="DVP"then Table.SelectRows(#"Reordered Columns",(x)=>x[Serial]=List.Max(Table.SelectRows(#"Reordered Columns",(x)=>x[Serial]<[Serial]and x[Type]<>"DVP")[Serial]))[cost_per_share]{0}else[price],Replacer.ReplaceValue,{"price"}), #"New"=Table.ReplaceValue(#"Replace Values", each [TA], each [qty]*[price],Replacer.ReplaceValue,{"TA"}) in #"New"Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.