Adding custom column to nested table

[big_ozzie1]

Is there a way to add a custom column to a nested table that references another column in the nested table? For example, I would like to add a custom "previousRow" column to the nested table in the picture that shifts the "externalLoad" column down one:

... "externalLoad"      "Index"     "Previousrow"

...              63.07                1            error/null

...              21.41                2                  63.07

iterated through all tables in "Index." I've been at this for a quite a while and may simply be misunderstanding the fundamental principles at play.

Code, dotted lines indicate an attempt at creating the column:

let
    Source = Excel.Workbook(File.Contents("C:\Users\Sam\Documents\ToyotaTest.xlsm"), null, true),
    UpperLowerbyDay_Sheet = Source{[Item="UpperLowerbyDay",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(UpperLowerbyDay_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"podID", type text}, {"AthleteName", type text}, {"Date", type date}, {"externalLoad", type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [podID] <> null and [podID] <> ""),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date", Order.Descending}, {"AthleteName", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"AthleteName", "Date"}, {{"Count", each _, type table [podID=nullable text, AthleteName=nullable text, Date=nullable date, externalLoad=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Index", each Table.AddIndexColumn([Count],"Index",1), type table[Index=number, externalLoad=number]),
---------------------------------------
   #"Added Custom1" = Table.AddColumn(#"Added Custom", "PrevRow", each Table.AddColumn([Index], "previousRow", (Innertable)=> each Table.AddColumn(Innertable(#"Added Custom"{[Index]}[externalLoad])))),
---------------------------------------
    #"Expanded Index" = Table.ExpandTableColumn(#"Added Custom", "Index", {"podID", "AthleteName", "Date", "externalLoad", "Index"}, {"podID", "AthleteName.1", "Date.1", "externalLoad", "Index.1"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Index",{{"Index.1", Int64.Type}, {"AthleteName.1", type text}, {"Date.1", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

 Thanks for the help.

[v-angzheng-msft]

Hi, @big_ozzie1 

Based on my research, the solution offered by @wdx223_Daniel  will work for you

= Table.TransformColumns(previousStep,
{"Index",each Table.FromColumns(Table.ToColumns(_)&{List.RemoveLastN({null}&[externalLoad],1)},Table.ColumnNames(_)&{"PreRows"})}
)

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wdx223_Daniel · Accepted solution]

NewStep=Table.TransformColumns( #"Grouped Rows",{"Count",each Table.FromColumns(Table.ToColumns(_)&{List.RemoveLastN({null}&[externalLoad],1)},Table.ColumnNames(_)&{"PreRows"})})

[Vijay_A_Verma]

This has to be done by using Function call. 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 source appropriately)

 #"Grouped Rows" = Table.Group(Source, {"Customer"}, {{"Temp", each _, type table [Customer=nullable text, Year=nullable text]}}),
    //Function Start
    fxProcessNext=(Tbl)=>
        let
            #"Added Index" = Table.AddIndexColumn(Tbl, "Index", 0, 1, Int64.Type),
            #"Added Custom" = Table.AddColumn(#"Added Index", "Previous Value", each try Tbl[Year]{[Index]-1} otherwise null)
        in
            #"Added Custom",
    //Function End
    #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "fxProcessNext", each fxProcessNext([Temp])),
    #"Expanded fxProcessNext" = Table.ExpandTableColumn(#"Invoked Custom Function", "fxProcessNext", {"Year", "Previous Value"}, {"Year", "Previous Value"})
in
    #"Expanded fxProcessNext"

[Anonymous]

I would add the indexes before the group step, and set up you previous row columns  based on self-join using the offset indexes as the keys. This will make the remaining steps trivially easy.

--Nate