Prev Value not working

[smpa01]

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 |

@CNENFRNL @AlexisOlson 

Thank you in advance.

[CNENFRNL]

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
    Rearraged

[AlexisOlson]

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:

https://community.powerbi.com/t5/Community-Blog/Power-Query-Methods-to-Refer-to-Previous-Row-Time-Pe...

[BA_Pete · Accepted solution]

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

[smpa01]

@BA_Pete  yup , completely forgot.

Sorry for this stupid Q

[BA_Pete]

Lol, easily done. 🙂👍

Pete