Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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 ?    ...
Need Help
Tips and Tricks
  • MFelix's avatar
    MFelix
    8 years ago

    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

  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    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)

     

QuantumWest's avatar
QuantumWest
Regular Visitor
4 years ago

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's avatar
Anonymous
Not applicable
3 years ago

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.