Getting previous row value without adding index column

[rashidanwar]

Hi everybody  
Hope that you all are good.

I have a dataset that need some transformations in power query. One of the transformations is getting the previous row value and the next row value. Right now I am adding the index column to achieve this.
But the downside of using the index column is that it making the query folding not happening. Actually I need the tranfromation in the way that it doesn't impact query folding so that I can use the incremental refresh feature. 

Let me know if there is a way to achieve it. Thank you.

Rashid Anwar

[rashidanwar]

@Vijay_A_Verma what if I have grouped the table and columns are appearing as sub tables and If I want to add the above formula in the sub table.

For example, the table looks like as follow and I want to add the value in each table of the Revenue column.
call_id       Revenue
1               Table
2               Table
2               Table

[Vijay_A_Verma]

I prefer function calling. Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMFTSUbJQitWBc4yROSbIHAMYxwjIMYVxjJH1gDhGyBxLZA7QtFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Revenue = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Year"}, {{"Temp", each _, type table [Year=nullable text, Revenue=nullable text]}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "fxGetPreviousValue", each fxGetPreviousValue([Temp])),
    #"Expanded fxGetPreviousValue" = Table.ExpandTableColumn(#"Invoked Custom Function", "fxGetPreviousValue", {"Revenue", "Previous Value"}, {"Revenue", "Previous Value"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded fxGetPreviousValue",{"Temp"}),
    //Function Start
    fxGetPreviousValue=(Tbl)=>
    let
        #"Previous Value" = Table.FromColumns(Table.ToColumns(Tbl) & {{null} & List.RemoveLastN(Tbl[Revenue],1)},Table.ColumnNames(Tbl)&{"Previous Value"})
    in
        #"Previous Value"
    //Function End
in
    #"Removed Columns"

[Vijay_A_Verma · Accepted solution]

Use this step. Replace #"Changed Type" with your previous step and Revenue with your column name

= Table.FromColumns(Table.ToColumns(#"Changed Type") & {{null} & List.RemoveLastN(#"Changed Type"[Revenue],1)},Table.ColumnNames(#"Changed Type")&{"Previous Value"})

 To see its working -Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the previous step and revenue column appropriately)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtFDSUTK3MFeK1QFzLYFcUzNTKNfIAMi1tIDxDIE8QwMjYxjfCMg3A/JiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Revenue = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Revenue", Int64.Type}}),
    #"Get Previous Value" = Table.FromColumns(Table.ToColumns(#"Changed Type") & {{null} & List.RemoveLastN(#"Changed Type"[Revenue],1)},Table.ColumnNames(#"Changed Type")&{"Previous Value"})
in
    #"Get Previous Value"

Edit - For next row value, you can use below construct, replace Get Previous Value with your previous step and Revenue with your column name

= Table.FromColumns(Table.ToColumns(#"Get Previous Value") & {List.RemoveFirstN(#"Get Previous Value"[Revenue],1)&{null}},Table.ColumnNames(#"Get Previous Value")&{"Next Value"})

[rashidanwar]

Great!
Thank you so much @Vijay_A_Verma. You are a rockstar!