Forum Discussion
Adding multiple columns in a nested table referring to previous row value
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
)
3 Replies
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 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.
