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 p...
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"})})

Vijay_A_Verma's avatar
Vijay_A_Verma
Icon for Most Valuable Professional rankMost 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"