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.

Alejandro Lopez-Lago - MSFT · Accepted Answer · 2016-07-08 21:48:53Z

8

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.

answered Jul 8, 2016 at 21:48

Alejandro Lopez-Lago - MSFT

4,18422 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Eugene Over a year ago

Thank you, Alejandro! As I understand, the formula (each [q] in the example) is same for all new columns?

Eugene Over a year ago

And how can I covert the whole list to text (I want to union it with another list with non-numeric column names)?

Alejandro Lopez-Lago - MSFT Over a year ago

[q] is value in that column, so every column in each row will have the same value, but the value between rows can be different. For converting the list to text, do you want to make each value in the list a text value, or do you want to represent the list as a text value? If it's the former, use Text.From.

Eugene Over a year ago

Thank you, Alejandro! This is exactly what I was looking for.

Ben Over a year ago

I got a little confused on the 'q' part because in my case I need null values. Based on Alejandro's answer if you wanted to add 15 user defined columns with null values, this formula will work:

Add_Multiple_Columns=List.Accumulate({1..5}, Source, (state, current) => Table.AddColumn(state, "UserDefined" & Number.ToText(current), each null))

Collectives™ on Stack Overflow

PowerQuery: Adding multiple columns

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related