Multiply column with several other columns individually

[Anonymous]

Hi,

I have 10 columns that I want to multiply with a column called FX.

For example, I have columns:

1. Sales

2. Salaries

3. Tax

4. FX rate

I want to new columns equal to:

1. Sales*FX rate

2. Salaries*FX rate

3. Tax*FX rate

What would be the most elegant way to do this? I am open to using power query or dax. I know how to do this in other programming languages (i.e. a simple loop) but am new to power BI, any help would be appreciated.

[Anonymous · Accepted solution]

Thanks, is there a way to do this programmtically:

For each col in my columns:

   new_col = col * multiplication column

   add new_col to table

[HotChilli · Accepted solution]

I've had a go.  You'll have to incorporate this into your Power Query code with your previous step names etc.

#"stepNext" = List.Accumulate(Table.ColumnNames(#"Inserted Multiplication"), #"Inserted Multiplication", 
    (state, current) => 
      Table.AddColumn(state, "XX " & current, each [Multiplication] * Record.Field(_, current), Int64.Type) )

There's no foreach in M, it's functional programming so we pass a list of column names to the function List.Accumulate and say what we want to do with those column names.

[v-xulin-mstf]

Hi @Anonymous

You can create three measures as:

Sales*FX rate=
Calculate
   (
     max('table'[Sales])*max('table'[FX rate]),
     'table'
    )

Salearires*FX rate=
Calculate
   (
     max('table'[Salearires])*max('table'[FX rate]),
     'table'
    )

Tax*FX rate=
Calculate
   (
     max('table'[Tax])*max('table'[FX rate]),
     'table'
    )

Best Regards,

Link

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Thanks, is there a way to do this programmtically:

For each col in my columns:

   new_col = col * multiplication column

   add new_col to table

[HotChilli]

You've basically shown how to do it right there.  Just go to Power Query->add a new column ,either custom column and add code manually or

use the interface (select 2 columns -> use the ribbon in the (standard->multiply) - in the 'From number' section.

You'll work it out