Adding multiple columns in a nested table referring to previous row value

[Duh]

Can someone save my day!!

I followed the below video to add a index to a nested table and add a column with the previous row value in the nested table, but the video only talks about adding row value of 1 column. How do I add row previous row values from multiple columns?

https://www.youtube.com/watch?v=IGF2-qfzDQs&t=775s

The M code I used is:

= Table.AddColumn(PreviousStep, "NestedTable",

       each

let
        AllDataTable = [Data],
        PrevAnswer =
              Table.AddColumn(
                    AllDataTable,"Prev Answer",
                    each try AllDataTable[Answer] {[Index]+1}
                    otherwise null),

 

let
        AllDataTable = [Data],

PrevDate =
        Table.AddColumn(
              AllDataTable,"Prev Date",
              each try AllDataTable[Date] {[Index]+1}
              otherwise null



in PrevAnswer,

in PrevDate

)

[AlienSx]

Hi, @Duh you need a list of column names and list of new column names. Then use Record.SelectFields (instead of referring to single record field like _[Answer]). Function f  below takes table as an argument. Use that function in Table.TransformColumns or Table.AddColumn as you like (final step). Smth like this.

    columns = {"Answer", "Date"},
    new_columns = List.Buffer(List.Transform(columns, (x) => "Prev " & x)),
    f = (tbl as table) =>
        [add_record_column = 
            Table.AddColumn(
                tbl, "Prev", 
                (x) => try Record.SelectFields(tbl{x[Index] - 1}, columns) otherwise null ),
        expand_record_column = Table.ExpandRecordColumn(add_record_column, "Prev", columns, new_columns)][expand_record_column],
    AddPrevData = Table.TransformColumns(PreviousStep, {"Data", f})

[Duh]

• @AlienSx Sorry I could not understand the solution. I have a long way to go to understand M. What I did was create 2 nested columns. Expanded one with the index 0 and the other with index 1. But I really want to understand the solution you provided. Do I add the code in a custom function?

[AlienSx]

@Duh I added 2 columns into your nested table with values from previous row. Try to replace your code with mine (together with the name of step) and see if it works. I am referring to the table generated by PreviousStep (you are referring to this step too) so you don't need to change anything in my code.