power query in Power bi

[kandre123]

Hi all. 

I have a very wide table (see attached link for data) the 3rd-310 column are figures in local currency. The last 10 columns contain currency rates. Is there a way of multiplying the 10 last columns into all the other columns by its differentiator, years? so i dont have to make duplicate columns for multiplication and expanding the width to 600+ columns?

I wanted to do the currency conversion before i flatten the table.

https://www.dropbox.com/sh/cp8p5lwtrrct9pc/AAD5lgcQHhPlB-pySumiTEn1a?dl=0

[dax · Accepted solution]

Hi kandre123,

I am not sure whether you want to show currency table's currency into fact table? Or want to multiply columns which in the FactAccounting?

If you want to calculate columns in the same table, you could create custom column, the use expression like [c1]*[c2] to achieve this goal. If you want to find corresponding column in currency table, then calculate this in FactAccounting table, you could try to add custom column to show currency value in FactAccounting table, then multiply two column. You could try to use M code like below

= Table.AddColumn(#"Changed Type", "Custom", each (let currentCustomer = [#"Valuta, 2011"] in Table.SelectRows(currency, each [CurrencyID]  = currentCustomer)){0}[2008])

Then use this column to multiply other column.

Best Regards,
Zoe Zhi

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