Forum Discussion

Kent_Heng's avatar
Kent_Heng
Frequent Visitor
8 years ago
Solved

Positive & Negative duplicate values

Hi All,   Just started using Power BI, and stumbled onto this accounting issue with the data.   I have one column "Cost" and it can have negative values, which i dont not want to display, as it a...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    There are two type conversions in my code.

     

    One is changing the type of cost from text to currency as when you manuall enter data by default is text typed. This is to allow numeric functions to be applied later such as sum average etc.

     

    The second transformations is inside the custom function.

    Number.Abs() requires that you enter a numeric type for it to work, so to ensure that it is a numeric type going in I have wrapped the column in Number.From() which converts to a numeric type.

     

    I do not think either of these steps should cause an error however you should ensure that the cost column is in either currency or decimal type just to be sure.

Kent_Heng's avatar
Kent_Heng
Frequent Visitor
8 years ago
Hi Thomas, Thanks for your reply. I have tried your step, by adding the custom column in Query editor. However, when i clicked ok, the new column is generated and all the numbers are "Error", with the following error message: Expression.Error: We cannot apply field access to the type Function. Details: Value=Function Key=Cost Am i missing another step prior to adding in the ABS formula?
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Are you trying with the code I posted above or are you trying to apply it to your own data?

  • Kent_Heng's avatar
    Kent_Heng
    Frequent Visitor
    8 years ago
    Hi Thomas, Following your steps in my own dataset. I did notice in your below table, where you convert the cost to currency type? Or something?
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      There are two type conversions in my code.

       

      One is changing the type of cost from text to currency as when you manuall enter data by default is text typed. This is to allow numeric functions to be applied later such as sum average etc.

       

      The second transformations is inside the custom function.

      Number.Abs() requires that you enter a numeric type for it to work, so to ensure that it is a numeric type going in I have wrapped the column in Number.From() which converts to a numeric type.

       

      I do not think either of these steps should cause an error however you should ensure that the cost column is in either currency or decimal type just to be sure.