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

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,

     

    • darentengmfs's avatar
      darentengmfs
      Post Prodigy
      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

      • abukapsoun's avatar
        abukapsoun
        Post Patron
        5 years ago

        I guess we are almost there, 

        I tried creating the formula:

        IF [Reference] = "X" then [P01]*(-1) else [P01]

         

        But I am getting the following error on P01: Token Eof expected. 

         

        Any idea?