Forum Discussion

Olha_Protsak's avatar
Olha_Protsak
Frequent Visitor
4 years ago
Solved

Multiply column in the same column using a filter or if clause

Hello Community,

 

I was wondering if there is a possibility to multiply columns by constant in the same column with using a FILTER?

 

So I have a banch of countries in my table, and I need to change several columns by multiplying it in 1000 but only for 1 country.

I know how to do it using ADD.Column feature in Power Query but I dont want to create new column I need it in the same one.

 

For Example  I have this table:

Legal Entity Label (Entity)Total of Capsules_L
Switzerland5000
Germany2000
Italy3000
Malta500
Spain6000
Netherlands1500
Austria3500

 

And I need to multiply values only for Germany.

I was trying to create this step:

 

= Table.TransformColumns(#"Filtered Rows", each if ([#"Legal Entity Label (Entity)"] = "Germany") then {{"Total of Capsules_L", each _ * 1000 , Currency.Type}} else "Total of Capsules_L") 

 

But I get this error:

Does anyone know how to solve the case? or there is another work around?

 

Thank you!

Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    Hi Olha_Protsak ,

     

    I think you want to use Table.ReplaceValue, something like this:

    = Table.ReplaceValue(
    previousStep,
    each [Total of Capsules_L],
    each if [Legal Entity Label (Entity)] = "Germany"
        then [Total of Capsules_L] * 1000
        else [Total of Capsules_L],
    Replacer.ReplaceValue,
    {"Total of Capsules_L"}
)

     

    Pete

4 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

    Hi Olha_Protsak ,

     

    I think you want to use Table.ReplaceValue, something like this:

    = Table.ReplaceValue(
    previousStep,
    each [Total of Capsules_L],
    each if [Legal Entity Label (Entity)] = "Germany"
        then [Total of Capsules_L] * 1000
        else [Total of Capsules_L],
    Replacer.ReplaceValue,
    {"Total of Capsules_L"}
)

     

    Pete

    • Olha_Protsak's avatar
      Olha_Protsak
      Frequent Visitor
      4 years ago

      yes that could work as well,

      but i have this message when using the code:

      I have checked and the spelling of columns are correct.

      Do you have the idea what is wrong?

      • BA_Pete's avatar
        BA_Pete
        Super User
        4 years ago

        Hi Olha_Protsak ,

         

        Sorry, I think it should be this because of the brackets:

        [#"Legal Entity Label (Entity)"]

         

        Pete