Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Regular Visitor

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

Regular Visitor

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:

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.

@RadosławPopraws 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)))
Super User
Super User

this code will add three null columns

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

Super User
Super User

Hi, @RadosławPopraws 

List.Accumulate(your_list, MyTable, (s, c) => Table.AddColumn(s, c, each null))
Solution Sage
Solution Sage

Hi @RadosławPopraws 


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?




Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors