Add multiple calculated Columns INSIDE Nested Table without add Custom Column outside, in Power Quer

[DSR]

Question Insert Multiple Columns in Nested Table in Power Query

[spinfuzer]

You can either do the formula directly in the AddCustomColumn or just create a custom function and call the custom function in either the Table.Group step or a TransformColumns after the grouping.

You can add the (add_columns) part as a custom function or just copy the formula from "let" to "in add_multiple_columns" directly in a Table.AddColumn.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsmvzC9JVNJRMgRhAyBhoGepFKuDJGMExKbYJIxBkli1mIBksOoBCRoj6/HIz0uB2W8KkTBDkTACOwybDMh+Q2NsMiD7DRGmxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Period = _t, Warranty = _t, #"WarrantyCalled After n periods" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Period", Int64.Type}, {"Warranty", Int64.Type}, {"WarrantyCalled After n periods", type number}}),
    add_columns = (tbl as table) =>
        let
            add_index = Table.AddIndexColumn(tbl, "Index",0),
            rows = List.Buffer(Table.ToRecords(add_index)),
            add_multiple_cols = 
            
            Table.ExpandRecordColumn(
                Table.AddColumn(add_index, "Custom", each 
                [
                    ColumnA = try rows{[Index]-1}[Warranty] * rows{[Index]-1}[WarrantyCalled After n periods] otherwise null,
                    ColumnB = try rows{[Index]-2}[Warranty] * rows{[Index]-2}[WarrantyCalled After n periods] otherwise null,
                    ColumnC = try rows{[Index]-3}[Warranty] * rows{[Index]-2}[WarrantyCalled After n periods] otherwise null
                ]
                ),
                "Custom",
                {"ColumnA", "ColumnB", "ColumnC"} 
            )
        in
            add_multiple_cols
            ,
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Category"}, {{"NestedTable", each add_columns(_), type table [Category=nullable text, Period=nullable number, Warranty=nullable number, WarrantyCalled After n periods=nullable number]}})
in
    #"Grouped Rows"

[DSR]

Question to

Thank you spinfuzer, I appreciate your time and advise.

I am new to Power query Syntaxes, Micosoft help site idoes not help in certain codes. I do not understand certain codes you use "

Table.ExpandRecordColumn(

 I will search. 

Question Can a Variable move with the Row Index:

Int=AllDataTable[Warranty]{[Index]} if yes, Does the Int AllDataTable{[Index]}[Warranty]  which one is correct?

or I have to write : Int1=AllDataTable[Warranty] and in separate line

Int=Int1{[Index]}

I simulated my real case to Cartable, but I wrote the following code and fine tune it, but still prefer to use

one Variable: Int=AllDataTable[Warranty]{[Index]} and use it in subsequent codes but I want it to change with the {index] value, is it possible?

I have used the following: but I will study your code first to understand the line (Before the function).

#"Grouped Rows" = Table.Group(SortedRows, {"Case", "CC", "Product"}, {{"FullTable", each _, type table}}),
Col.IndexSub = Table.TransformColumns(#"Grouped Rows",{{"FullTable", each Table.AddIndexColumn(_,"IndexSub") }} ),

Col.WarrantyCalled = Table.AddColumn(Col.IndexSub , "ColWrntTb", each
let
AllDataTable = [FullTable],
WPrv= AllDataTable[Provision Warranty],
WPrd= AllDataTable[PeriodNo],
WCalledinNPrd= AllDataTable[Wrnt called After n Periods],
WInt= Number.IntegerDivide(WCalledinNPrd,1),
WMod = Number.Mod(WCalledinNPrd,Number.Sign(WCalledinNPrd)),

Col.WarrantyCalledValue= Table.AddColumn(AllDataTable, "WarrantyCalledValue", each
if WPrd{[IndexSub]} = 0 or WPrd{[IndexSub]} <= WCalledinNPrd{[IndexSub]} then 0 else

(if WCalledinNPrd{[IndexSub]} <=1 then
1/1* (if Number.IntegerDivide(WCalledinNPrd{[IndexSub]},1)+1= WPrd then
WPrv{[IndexSub]}*(1- WPrd{[IndexSub]}) else
WPrv{[IndexSub]}*(1- WPrd{[IndexSub]})+(try WPrv{[IndexSub]-1} otherwise null)* WPrd{[IndexSub]}) else

2/2*(if WInt{[IndexSub]}+1= WPrd{[IndexSub]} then
(try WPrv{[IndexSub]- WInt} otherwise null )*(1-WMod) else
(try WPrv{[IndexSub]- (WInt+1)} otherwise null )*WMod +
(try WPrv{[IndexSub]- WInt } otherwise null )*(1-WMod))))


in
Col.WarrantyCalledValue),
#"Expanded ColWrntTb" = Table.ExpandTableColumn(Col.WarrantyCalled, "ColWrntTb",

[spinfuzer]

AllDataTable[Warranty]{[Index]} or AllDataTable{[Index]}[Warranty] can both work as long as [Index] is a proper reference in the outer table.  However you are working with nestedtables so I think you really wanted the nested index so the above does get you want you want in your sceneario. 

If Index was indeed an outer index that you wanted to use.  You start with a Table, then get the Row and then column.  Or start with the Table, get the column and then row.  Just be consistent so you don't confuse yourself.  I personally did not do it that way because it is slower performance when you refererence an entire table and then drill down into single value. If you plan to do reference a table, then add a step buffer_name = Table.Buffer(AllDataTable) and reference buffer_name[Warranty]{[Index]} instead and you will notice better performance.  

if you want to add multiple columns at once, you create a record inside of the AddColumn e.g.

Table.AddColumn(Source, "Custom Col Name", each [ Col1 = "value1", Col2 = "value2"]).  Then you can expand the record column and Col1 and Col2 become columns of the table.

[DSR]

Concerning adding multiple Columns inside the nested table (not outside), I have tried your method it still give error. I appreciate steps not Function to execute

[spinfuzer]

If you are using a function parameter inside of another function parameter (e.g. Table.AddColumn inside of a Table.TransformColumns then you have to replace a second each with (x) =>.

each _ represents the tables in each row in Table.TransformColumns

and (x) => x represents the rows of the table _ in Table.AddColumn

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsmvzC9JVNJRMgRhAyBhoGepFKuDJGMExKbYJIxBkli1mIBksOoBCRoj6/HIz0uB2W8KkTBDkTACOwybDMh+Q2NsMiD7DRGmxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Period = _t, Warranty = _t, #"WarrantyCalled After n periods" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Period", Int64.Type}, {"Warranty", Int64.Type}, {"WarrantyCalled After n periods", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Category"}, {{"NestedTable", each _, type table [Category=nullable text, Period=nullable number, Warranty=nullable number, WarrantyCalled After n periods=nullable number]}}),
    add_index = Table.TransformColumns(#"Grouped Rows", {"NestedTable", each Table.AddIndexColumn(_, "Index")}),
    add_cols = Table.TransformColumns(add_index, 
    {
        "NestedTable",
        each 
        Table.AddColumn(_, "warranty", (x) => 
        [
            ColumnA = try _{x[Index]-1}[Warranty] * _{x[Index]-1}[WarrantyCalled After n periods] otherwise null,
            ColumnB = try _{x[Index]-2}[Warranty] * _{x[Index]-2}[WarrantyCalled After n periods] otherwise null,
            ColumnC = try _{x[Index]-3}[Warranty] * _{x[Index]-3}[WarrantyCalled After n periods] otherwise null
        ]
        )
    }
    )
in
    add_cols

You can choose to expand the record column after expanding the table, or just add Table.ExpandRecordColumn within the add_col step directly.  

[DSR]

Thank you spinfuzer for all your time and advise. I will try the last one.

Another question: can a variable varries where one of its compoment varies?

UnearnedRev.Prv= AllDataTable[Unearned Rev] {[IndexSub]},

Can "UnearnedRev.Prv" varies with "{[IndexSub]}"? or I have to write:

UnearnedRev.Prv= AllDataTable[Unearned Rev]   and 

UnearnedRev.Prv{[IndexSub]} 

to work

I tried it in the followiong  if gives error: "Expression.Error: The field 'IndexSub' of the record wasn't found."

I do not know the reason, is it from the stucture above, it does not vary or my code mistake?

Col.UnearnedRevMaterialized = Table.AddColumn(Col.DoubtfulDebtAudited, "ColUnearnedRevMaterializedTb", each

    let

    AllDataTable = [ColDoubtfulDebtWrittenOffTb],

    UnearnedRev.Prv= AllDataTable[Unearned Rev] {[IndexSub]},

    UnearnedRev.Prd = AllDataTable[PeriodNo] {[IndexSub]},

    UnearnedRevMaterializedinNPrd = AllDataTable[Unearned Rev Months collected in advance] {[IndexSub]},

    UnearnedRevMaterializedinNPrd.Int = Number.IntegerDivide(UnearnedRevMaterializedinNPrd,1),

    UnearnedRevMaterializedinNPrd.Mod = Number.Mod(UnearnedRevMaterializedinNPrd,Number.Sign(UnearnedRevMaterializedinNPrd)),

    UnearnedRevMaterializedinNPrd.Rounded = Number.RoundAwayFromZero (UnearnedRevMaterializedinNPrd),

    Col.UnearnedRevMaterializedValue= Table.AddColumn(AllDataTable, "UnearnedRevMaterializedValue", each

    if UnearnedRev.Prd = 0 or UnearnedRev.Prd < UnearnedRevMaterializedinNPrd then 0 else

        UnearnedRev.Prv*.5)

in

Col.UnearnedRevMaterializedValue),

[spinfuzer]

If [IndexSub] is coming from your nested table, then no, you can't just do AllDataTable[Unearned Rev] {[IndexSub]}.  Carefully review my examples, any time I am referring to a nested column I am prefixing it with x because my iterator within the Table.AddColumn is   Table.AddColumn(NestedTableColumn, (x) => ......).  If I want to access the nested colum IndexSub I would write x[IndexSub].