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.
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.
Solved! Go to 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
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.
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.
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.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |