Forum Discussion

kandre123's avatar
kandre123
Advocate I
6 years ago
Solved

power query in Power bi

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

 

Need Help
  • dax's avatar
    dax
    6 years ago

    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.

1 Reply

  • dax's avatar
    dax
    Community Support
    6 years ago

    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.