Forum Discussion
Prev Value not working
I am trying to get the previous row value and this is what I tried which is not working
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc2xCQAxDEPRXVwfyJITLpnFZP81UtopH/qgTCOCkHPa+dIErdLAKEyoI7z0926BHdolOuJVS8nnm6r1XA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Merged = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Merged", type date}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each let
v = [Index],
x = v-1,
y = #"Added Index"[Merged]
in
if x=0 then null else y{x})
in
#"Added Custom"
The above is returning the following
I was hoping the code to return
| Merged | Index | Custom |
|------------|-------|------------|
| 1/31/2015 | 1 | null |
| 2/28/2015 | 2 | 1/31/2015 |
| 4/4/2015 | 3 | 2/28/2015 |
| 5/2/2015 | 4 | 4/4/2015 |
| 5/30/2015 | 5 | 5/2/2015 |
| 7/4/2015 | 6 | 5/30/2015 |
| 8/1/2015 | 7 | 7/4/2015 |
| 8/29/2015 | 8 | 8/1/2015 |
| 10/3/2015 | 9 | 8/29/2015 |
| 10/31/2015 | 10 | 10/3/2015 |
| 11/28/2015 | 11 | 10/31/2015 |
| 12/31/2015 | 12 | 11/28/2015 |
Thank you in advance.
Hi smpa01 ,
My immediate guess is you've forgotten that Power Query rows are zero-base. Therefore, when you apply 'y{x}', you're just rturning back to the same row reference.
The easiest way to fix would be to create your [Index] column starting from zero and amend your result to:
if x < 0 then null else y{x}Pete
5 Replies
Just FYI, this approach of getting the previous value by index lookup is really inefficient for large tables.
Vijay_A_Verma has a nice blog post related to this:
Give that the original data is already sort, the solution is tricky and simple enough,
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc2xCQAxDEPRXVwfyJITLpnFZP81UtopH/qgTCOCkHPa+dIErdLAKEyoI7z0926BHdolOuJVS8nnm6r1XA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Merged = _t]), Rearraged = let col = Source[Merged] in Table.FromColumns({col,{null}&List.RemoveLastN(col)}) in RearragedHi smpa01 ,
My immediate guess is you've forgotten that Power Query rows are zero-base. Therefore, when you apply 'y{x}', you're just rturning back to the same row reference.
The easiest way to fix would be to create your [Index] column starting from zero and amend your result to:
if x < 0 then null else y{x}Pete
