Forum Discussion

xenon325i's avatar
xenon325i
Regular Visitor
8 years ago
Solved

Absolute Value of data

Hi all, first post here :smileyhappy:

 

I'm trying to show some sales data but because my data comes from an invoice table the values are all negative which doesn't look nice in my report. I'm using Direct Query as I need this data to be as live (ish) as possible. How can I change these values to be positive (ABS Value)?

 

I tried to click on the column header to change but it tells me I have to swap to import mode and I don't really want to do that. I tried to put it directly in the Query Editor using "Select ABS([Amount]) FROM...." hoping PowerBI wouldn't notice but it did, and again told me to switch to import mode to do this.

 

Is there any way to change the sign of this data in Direct Query Mode?

 

Thanks!

  • xenon325i's avatar
    xenon325i
    8 years ago

    Sorry I sent the wrong sample :smileyembarrassed:

     

    I will use your work-around by adding a column at source.

     

    Thank you for your help.

     

18 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    xenon325i,

    You can create a custom column in Query Editor of Power BI Desktop using the code below.

    Number.Abs([Amount])

    There is an example for your reference.


    Regards,
    Lydia

    • xenon325i's avatar
      xenon325i
      Regular Visitor
      8 years ago

      Thanks Lydia but I already tried that - you can't appear to add a custom column in Direct Query mode:

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        xenon325i,

        Do you connect to SQL Server database? What is the data type of  the Amount column in SQL table?

        Regards,
        Lydia