Forum Discussion
Capture the value of previous row in Power Query]
Hi,
I was trying to work on a use case where I need to capture the previous row value.
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. So basically we only want to calculate the previous row value when the type is DVP otherwise it should show the price.
I have the manual calculation in excel I just want to replicate the same in power bi.
Would really appreciate it if someone can help me with this.
MFelix amitchandak Anonymous
Thanks
Excel and PBI Files
https://docs.google.com/spreadsheets/d/1LQzuohqY8A5KrQre_FdbeJZzh8EvMWEm/edit?usp=sharing&ouid=101701281295966826492&rtpof=true&sd=true
https://drive.google.com/file/d/13J5tKK5epRCrr0ZADWFOjjCz37fZyqdj/view?usp=sharing
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.
11 Replies
Hi 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 _ 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 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=trueHi 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.
Here is three methods to refer to previous row, perhaps one of them fits your case:
https://gorilla.bi/power-query/get-previous-row-value/
