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)

     

StockTraitor's avatar
StockTraitor
Advocate I
3 years ago

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's avatar
MFelix
Super User
3 years ago

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.