PowerQuery: Adding multiple columns

Question

Little introduction.

Recent task: add 15 columns with same value.

Solution: Instead of consecutive use of Table.AddColumn, I applied following method.

= Table.RemoveColumns(
    Table.SplitColumn(
        Table.AddColumn(TableFromPreviousStep, "q", each "1")
        , "q"
        , Splitter.SplitTextByDelimiter(" ")
    , {"temp","q1", "q2", "q3", "q4", "q5", "q6", "q7", "q8", "q9", "q10", "q11", "q12", "qY+1", "qY+2", "qY+3"} 
    , 1)
, {"temp"})

Explanation: first I add temporary column named "q" with any default text value to the table. Then this column is split with space as delimiter (that's why it requires text), but since there is no spaces, it returns as many columns, as names count for them. First column retains original value and should be removed in a later step. All other new columns have default value of 1.

Question: My current task is creating several columns with values, calculated by a same formula. I try the same approach as above, but I fail to use any formula as default value. The best I've got is resulting columns containing a formula.

Any help is appreciated.

Solution

You can use List.Accumulate to add as many columns to the table. If you have a table Source with the column q, you can create 15 columns with the same value with this formula:

List.Accumulate({1..15}, Source, (state, current) => Table.AddColumn(state, "q" & Number.ToText(current), each [q]))

Accumulate will loop through each number in the list, adding a column "qnumber" to the table it's been building up with Table.AddColumn. This will work with any value and any number of rows.