HELP: Generate rows with calculated values which depend on parameters

Hi Tamarah_, You can use this as an example, which you can adopt to your scenario:

let
    PricingTable = 
        let 
            Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmMgNgFiUxDfwMhYKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Bucket 1" = _t, #"Bucket 2" = _t, #"Bucket 3" = _t, #"Bucket 4" = _t, #"Bucket 5" = _t, FY = _t]),
            #"Changed Type" = Table.TransformColumnTypes(Source,{{"Bucket 1", Int64.Type}, {"Bucket 2", Int64.Type}, {"Bucket 3", Int64.Type}, {"Bucket 4", Int64.Type}, {"Bucket 5", Int64.Type}, {"FY", Int64.Type}})
        in #"Changed Type",
    IndexTable = 
        let 

            Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlHSUTLUM7BQitUB803BfEMDGN8MIm+qFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FY = _t, Index = _t]),
            #"Changed Type" = Table.TransformColumnTypes(Source,{{"FY", Int64.Type}, {"Index", type number}})
        in #"Changed Type",
    Process = List.Accumulate(  Table.ToRecords(IndexTable), 
                                Table.ToRecords(PricingTable),  
                                (a, n) => a & 
                                    {
                                        Record.TransformFields(
                                            List.Last(a), 
                                            List.Transform(
                                                List.Select(
                                                    Table.ColumnNames(PricingTable), 
                                                    each Text.Contains(_, "Bucket")
                                                ), 
                                                each {_, (x) =>  x * n[Index]}
                                            ) & 
                                            {{"FY", each n[FY]}} 
                                        ) 
                                    }  
                                ),
    Convert = Table.FromRecords(Process, Value.Type(PricingTable))
in Convert

Just copy and paste it into a Blank Query to see how it works.

The main work is happening in the Process step, which:

1. Filters column names from the main table using "bucket" as a driver, we need this to further apply indexing. (List.Select)

2. Generated a transformaiton rules for all bucket (using the list created above) and FY columns (List.Transform)

3. Applies rules on the last current row in the main table and append the result to the bottom of the table (List.Accumulate)

Kind regards,

John