Creating Dynamic Columns in Power Query with Expression Lookups

Question

I have a Source file that has a set of columns, each of binary type (true, false). I then have a processing (transform) table which has one or more rows, each row specifying a new column to be added to the Source, along with the expression to use to populate that column based on existing columns. In Power Query, I want to be able to extend the Source table with these additional column definitions. There can be any number of transform rows, so I would need to do this dynamically based on row count. Click on link below for an illustration of what I'm shooting for.

I believe this can be achieved using List.Accumulate in Power Query, but I haven't figured out exactly how to do it. Any suggestions?

Answer 1

Here is a method using List.Generate:

let
    Transform = Excel.CurrentWorkbook(){[Name="Transform"]}[Content],
    #"Transform Table" = Table.TransformColumnTypes(Transform, {{"Col Name", type text},{"Col Formula", type text}}),

    Source = Excel.CurrentWorkbook(){[Name="Source"]}[Content],
    #"Source Table" = Table.TransformColumnTypes(Source,{{"A", type logical}, {"B", type logical}, {"C", type logical}}),

    addCol = List.Last(
        List.Generate(
        ()=>[c=Table.AddColumn(#"Source Table", #"Transform Table"[Col Name]{0}, 
                Expression.Evaluate("each " & #"Transform Table"[Col Formula]{0}), type logical), 
                idx=0],
        each [idx] < Table.RowCount(#"Transform Table"),
        each [c=Table.AddColumn([c], #"Transform Table"[Col Name]{[idx]+1}, 
                Expression.Evaluate("each " & #"Transform Table"[Col Formula]{[idx]+1}), type logical), 
                idx=[idx]+1],
        each [c]))
in
    addCol