Skip to main content
cancel
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.

Reply
RadosławPopraws
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

5 REPLIES 5
RadosławPopraws
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:
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.

@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)))
wdx223_Daniel
Super User
Super User

this code will add three null columns

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

AlienSx
Super User
Super User

Hi, @RadosławPopraws 

List.Accumulate(your_list, MyTable, (s, c) => Table.AddColumn(s, c, each null))
JoeBarry
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?

 

Thanks

Joe

Helpful resources

Announcements
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

PBI_APRIL_CAROUSEL1

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