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 valu...
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})
- 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?
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.