Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

PowerQuery in Excel - adding columns based on a List.

Hi all,

I have a question.
So Let say I have Table with any data,
then I have a list with Columns I wish to add:

{"NewCol1", "NewCol2", "NewCol3"}
the list is manualy verified (  what ccan you do? eh... )

so Now I need to add thoose columnsto MyTable.

any help would be appriciead

Need Help
Show and Tell

5 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    2 years ago

    this code will add three null columns

    =Table.SelectColumns(MyTable,Table.ColumnNames(MyTable)&{"NewCol1", "NewCol2", "NewCol3"},2)

  • JoeBarry's avatar
    JoeBarry
    Solution Sage
    2 years ago

    Hi Anonymous 

     

    You will need to provide more information,

     

    What data is in the original table?

    What data is in the new columns and how are they related to the original table?

     

    Thanks

    Joe

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago

    Hi, Anonymous 

    List.Accumulate(your_list, MyTable, (s, c) => Table.AddColumn(s, c, each null))
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    ok, let me provide a simple example.
    Let say I have a Table:
    amount in local Currency

    exchange rate 2
    exchange rate 3
    some other columns.

    and now my Column list:
    NewColumnExRate1
    NewColumnExRate2
    NewColumnExRate2

    Calculations:
    if columns do not exists in original table:
    NewColumnExRate1 = amount in local Currency * exchange rate 1

    NewColumnExRate2 = amount in local Currency * exchange rate 2
    NewColumnExRate3 = amount in local Currency * exchange rate 3

    this is either / or logic,
    either all of them will exists or none of them, so there will be no need to do conditional addding of columns.

    I tried using AlienSx solution but Igot defeated and could not implement addding columns with calculations, just empty ones.

    • AlienSx's avatar
      AlienSx
      Super User
      2 years ago

      Anonymous this checks if one of column names from your list is already there. If not then all 3 columns are added

          rates = 
        [NewColumnExRate1 = each [amount in local Currency] * [exchange rate 1],
        NewColumnExRate2 = each [amount in local Currency] * [exchange rate 2],
        NewColumnExRate3 = each [amount in local Currency] * [exchange rate 3]],
    add_columns = 
        if List.Contains(Table.ColumnNames(MyTable), "NewColumnExRate3") 
        then MyTable 
        else List.Accumulate(Record.FieldNames(rates), MyTable, (s, c) => Table.AddColumn(s, c, Record.Field(rates, c)))