Forum Discussion

big_ozzie1's avatar
big_ozzie1
Frequent Visitor
4 years ago
Solved

Adding custom column to nested table

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.

Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    4 years ago

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

4 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    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"

     

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    4 years ago

    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.