Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

Add multiple columns in a single step (Power Query)

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

2 ACCEPTED SOLUTIONS
MFelix
Super User
Super User

Hi @Anonymous,

 

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:

 

Append.png

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

MarcelBeug
Community Champion
Community Champion

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)

 

Specializing in Power Query Formula Language (M)

View solution in original post

19 REPLIES 19
StockTraitor
Regular Visitor

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

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. 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



QuantumWest
Regular Visitor

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
Not applicable

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
Not applicable

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
Frequent Visitor

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)),

 

 

2020-05-28_18-00-29_PQ_Add_Columns.png

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

 

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

Thank you for posting!

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!

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.

Hi @RollKoll ,

 

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.

 

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:

MFelix_0-1644405662746.png

 

MFelix_1-1644405680027.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Thanks for an option 😉 works

P.S.

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

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?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

MarcelBeug
Community Champion
Community Champion

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)

 

Specializing in Power Query Formula Language (M)
MFelix
Super User
Super User

Hi @Anonymous,

 

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:

 

Append.png

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.