Forum Discussion
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
Thanks
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:
Regards,
MFelix
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)
19 Replies
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
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.
Your solution just saved me hour (or two) of typing. Had to create a lot of columns, and this worked.
Thank you for posting!
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
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.
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:
Regards,
MFelix
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)
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.
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"})
