Capture the value of previous row in Power Query]

[Anonymous]

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

[TimoRiikonen]

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/

[v-yanjiang-msft]

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 _ kalyj

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

[Anonymous]

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=10170...

[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 _ kalyj

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

[Anonymous]

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=10170...

[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 _ kalyj

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

[Anonymous]

Hi @v-yanjiang-msft ,

Thank you for the update. I guess we also need to replace [Total_cost],[cost_per_share] with the new calculation as it should show this:

At serial no.6 it should show 10,857 in the Total_cost column and the cost per share will be 14.48.

Can you please update that I tried but it is giving me an error.

Thanks
Anany

[v-yanjiang-msft]

Hi @Anonymous ,

According to your description, I update the code here:

et
    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"}),
    #"Replace Values2"=Table.ReplaceValue(#"Replace Values", each [TA], each [qty]*[price],Replacer.ReplaceValue,{"TA"}),
    #"Replace Values3"=Table.ReplaceValue(#"Replace Values2",each [Total_cost],each List.Sum(List.Range(#"Replace Values2"[TA],0,[Serial])),Replacer.ReplaceValue,{"Total_cost"}),
    #"Replace Values4"=Table.ReplaceValue(#"Replace Values3",each [cost_per_share],each [Total_cost]/[Total_holdings],Replacer.ReplaceValue,{"cost_per_share"})
    
in
    #"Replace Values4"

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.

[Anonymous]

Thank you so much for the quick update @v-yanjiang-msft but now the price is wrong or I should say hasn't been updated with the new calculation. It should show 14.38 in the last 2 rows as the type is dvp so it should be replaced with cost_per_share of the last rvp i.e 14.38 as we did for the above 3 rows. 

Now you see basically it is creating dependency it is possible in excel (attached manual cal above) but I'm wondering if that is even possible in Power BI. 

[v-yanjiang-msft · Accepted solution]

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 _ kalyj

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

[Anonymous]

Exactly. Anyways thank you so much for looking into this and spending valuable time on this. I will accept this as a solution. 

Thanks
Anany