Forum Discussion

abukapsoun's avatar
abukapsoun
Post Patron
5 years ago
Solved

Multiplying values

Hi, Need your support please. I have the following Table   Reference P01 P02 X -1000 500 Y 800 -600                 How can i transform the sign of values based on ...
Need Help
  • darentengmfs's avatar
    darentengmfs
    5 years ago

    Hi abukapsoun 

     

    There are 2 ways to do this.

     

    1. You can unpivot the columns, then create the new column with the IF function in DAX
    2. Or, you can create the IF function in Power Query Editor before unpivoting: Power Query > Add Column > Custom Column > Formula: if [Reference] = "X" then [P01]*-1 else [P01]

     

     

    **Update: formula amended as needed

darentengmfs's avatar
darentengmfs
Post Prodigy
5 years ago

Hi abukapsoun 

 

You can create a new column with IF function.

 

P01-New = IF('Table'[Reference] = "X", 'Table'[P01]*-1,'Table'[P01])

P02-New = IF('Table'[Reference] = "X", 'Table'[P02]*-1,'Table'[P02])

  • abukapsoun's avatar
    abukapsoun
    Post Patron
    5 years ago

    Thank you very much, will give it a try.

    Will I be able to unpivot the columns P01- New and P02 New ?

     

    • darentengmfs's avatar
      darentengmfs
      Post Prodigy
      5 years ago

      abukapsoun 

       

      If this is a query, you can always unpivot it in Power Query mode.

       

      Power Query > Transform > Unpivot Columns

      • abukapsoun's avatar
        abukapsoun
        Post Patron
        5 years ago

        Hi

         

        I have created the new columns and it is working like a charm. However when I go to query editor to unpivot the columns, the newly created column does not appear in the table. I assume because they were created from a formula ? But then how can I unpivot them ?

        I want to have them in the form of:

         

        Thanks,