Add multiple columns in a single step (Power Query)

[NatashaSchuster]

Hello, 

I have a table with one column and I need to create multiple additional blank columns in the same table, so it can be appended to another table. What would be the  M logic for this ? 

It would be nice to have a separate functions for that too like this - Table.AddColumns   

The closest answers I found are listed below: 

https://stackoverflow.com/questions/38251421/powerquery-adding-multiple-columns

https://social.technet.microsoft.com/Forums/en-US/c8d43a16-fea7-4a7c-bafd-fd0170b4e147/add-multiple-...

Thanks

[MFelix · Accepted solution]

Hi @NatashaSchuster,

To what I could understood you wnat to append columns if you append two columns that have different columns the ones that don't exist in the other table will automaticly be filled with nulls so you don't need to create those addtional columns:

Regards,

MFelix

[StockTraitor]

Sharing this here because the accepted solution didn't work for me.

Step 1: Power BI Power Query

Step 2: Advanced Editor

Step 3: Advanced Editor may look like this:

Let

   Source = ...

   #"//most recent step//"

   #"//new column name//" = Table.AddColumn(#"//most recent step//", "//new column name//", each ""),

   #"//new column name2//" = Table.AddColumn(#"//new column name//", "//new column name2//", each ""),

   #"//new column name3//" = Table.AddColumn(#"//new column name2//", "//new column name3//", each "")

In

   #"//new column name3//"

Step 4: Hit Done and enjoy 😊

Notes:

• You shouldn't need to edit the Source
• Anything within // indicates the desired or most recent step/column name
• (each "") avoids null values and just has everything in the column added as <blanks>
• This is one "step" that will result in adding x amount of steps in Power Query for however many columns you want to add

[MFelix]

Hi @StockTraitor

Be aware that the initial requirement is to combine two tables and when the column does not exist fill with blank or nulls that happens automatically when you append the query no need for adittional editing in the advance editor. 

[QuantumWest]

I had this need and I add a custom column manually and in the step I get this:

= Table.AddColumn(#"Grouped Rows", "Activities Created", each 0)

Then I just edit that as follows:

= Table.AddColumn(#"Grouped Rows", "Activities Created", each 0)
& Table.AddColumn(#"Grouped Rows", "Accounts Created", each 0)
& Table.AddColumn(#"Grouped Rows", "Accounts Modified", each 0)
& Table.AddColumn(#"Grouped Rows", "Contacts Created", each 0)
& Table.AddColumn(#"Grouped Rows", "Contacts Modified", each 0)
& Table.AddColumn(#"Grouped Rows", "Opportunities Created", each 0)
& Table.AddColumn(#"Grouped Rows", "Opportunities Modified", each 0)
& Table.AddColumn(#"Grouped Rows", "Cases Created", each 0)
& Table.AddColumn(#"Grouped Rows", "Cases Modified", each 0)

Worked like a charm for doing basically the same thing as a UNION ALL in SQL

[Anonymous]

Note each time function Table.AddColumn is called, all rows for existing columns will be included once in the resulting data. Therefore if above codes are used, the resulting data will have all rows repeated nine times for all existing columns, whereas those rows have one set of valid values for new columns created in above codes.

[Anonymous]

I used split column command and defined new column names directly in the code.

= Table.SplitColumn(Source, "Austria", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Austria","Belgium","Bulgaria","Denmark","Egypt","Germany","Hungary","Ireland","Netherlands","Poland","Portugal","Russia","Spain","United Arab Emirates","United Kingdom","Czech Republic","Finland","Morocco","Romania","Saudi Arabia","Slovakia","Switzerland","France","Italy","Latvia","Sweden","Russia Manufacturing"})

[Bsacheri]

If you want to add 5 null columns this syntax will work:

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

Help from:   https://stackoverflow.com/questions/38251421/powerquery-adding-multiple-columns 

[TomDegler]

Your solution just saved me hour (or two) of typing. Had to create a lot of columns, and this worked. 

Thank you for posting!

[ChemEnger]

Nice one @Bsacheri !

I too stumbled across your answer when looking for something else and have made use of the code.  Just adding that it doesn't have to be a number sequence in the List.Accumulate, I have used

List.Accumulate({"A","B","X","Y"}...

And a conditional statement within the Table.AddColumn - all works really well!

[RollKoll]

I tried this oneliner, and sadly failed...

= Table.AddColumn(#"Grouped Rows", List.Accumulate({"B","X","Y"}), each "AZ12345")

caused an error,

Expression.Error: 1 arguments were passed to a function which expects 3. Details: Pattern= Arguments=[List]

I am quite new in this field... Can you please help with it?
I simply want to add 4 or more columns in one line.

[MFelix]

Hi @RollKoll ,

What do you want to do? Do you want to add an additional line to your code with specif values?

[RollKoll]

I simply need to add 1 Column with values "CD" in each row and 3 more empty columns (spaceholders).
In ideal, name or rename them in one go also.
I want to minimise the amount of steps, - as later it will be repeated in around 10 different tables.

[MFelix]

Hi @RollKoll ,

Add a new column with the following code:

Table.FromRecords({
    [Column1 = "CD", Column2 = "", Column3 = "", Column4 = ""]
})

You can use other names insted of Colum1 , ..,. Column4 then just expand the columns:

[RollKoll]

Thanks for an option 😉 works

P.S.

Kinda funny... such a simple task, has to be done with a "workaround"

[MFelix]

This is not a workaround, you wanted to add all the values at once so we are grouping 4 steps into one just that.

Is the same that in excel you wanted to add the 4 columns to each table but not doing it mannually you would create a VBA macro to do it right?

[RollKoll]

I called it "workaround", as the function Table.FromRecords is not adding Columns directly, but rather merges two tables.
Original + newTable.

Anyway, it works nicely and what I needed.
Thanks again

[MFelix]

This is because Power Query works with tables and not with rows or columns, all your results in power query are always viewed in a table perspective, so all the steps when you add values is allways based on a table function, in this case because you want to add several columns at the same time, so you create the table and then merge it.

[Bsngr]

Maybe not what OP was searching for, but totally helped me with something I was looking for. Thanks @Bsacheri !

[MarcelBeug · Accepted solution]

This post was concurrent with MFelix' post.

Actually I don't understand the requirement, as you can just append tables with different columns.

Null values will be added automatically.

Anyhow if you still want to add multiple blank columns (I hope null is also fine) then you can use Table.SelectColumns with 3rd argument MissingField.UseNull (see bottom query below).

All 4 queries below consist of 1 line each.

Query Table5Columns:

= #table(5,{{1..5}})

Query Table1Column:

= #table(1,List.Zip({{1..3}}))

Query Appended:

= Table5Columns&Table1Column

Query Table1AddedColumns:

= Table.SelectColumns(Table1Column,Table.ColumnNames(Table5Columns),MissingField.UseNull)

[MFelix · Accepted solution]

Hi @NatashaSchuster,

To what I could understood you wnat to append columns if you append two columns that have different columns the ones that don't exist in the other table will automaticly be filled with nulls so you don't need to create those addtional columns:

Regards,

MFelix