Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 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 y...
Need Help
Show and Tell
Anonymous's avatar
Anonymous
Not applicable
3 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
    Icon for Super User rankSuper User
    3 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)))