In accounting and finance, negative numbers are used to represent losses or expenses, while positive numbers represent profits or income. In many cases, however, converting negative numbers to positive numbers is necessary for reporting or analysis purposes.

Converting numbers from negative to positive is a very easy task to do. It can be done with Power Query’s interface or using the M code. You can also convert negative numbers to positive numbers in the Power BI using DAX queries.

In this article, you will learn how to use both these approaches to convert negative numbers to positive effectively.

Convert Negative Numbers to Positive Numbers with Power Query

Power Query is an integral component of Power BI, offering a versatile toolset for data transformation and reshaping. With its extensive menus and options, Power Query empowers you to perform various transformations on your data effortlessly.

Aside from its graphical menu options, Power Query also uses the M query. The M query or language is a native data transformation language that further enhances and capability and flexibility of Power Query to execute diverse transformations.

In this section, you will learn how you can convert negative numbers to positive numbers using different options in Power Query.

Convert Negative Numbers to Positive Numbers with the Transform Option

The Transform option is not one of the menus that pops right at you when you are in the Power Query interface. That’s because it’s not available on the menu ribbons. To use it, you need to select the column you want to work on and right-click on it.

After selecting the Transform menu, click on the Absolute Value option that displays in the contextual menu. This will remove all the negative signs in the selected column.

Convert Negative Numbers to Positive Numbers with Column from Examples

The Column from Examples feature in Power Query is a powerful tool that allows you to add new columns quickly and easily to your data based on examples.

This is especially useful when you know what data you want in the new column, but you’re not sure how to create it using traditional Power Query transformations.

To use the Column from Examples feature, simply select the Add Column tab in the Power Query ribbon and then click Column from Examples. From there, you will have two options – From All Columns and From Selection.

The From All Columns option indicates that you want to take examples from any/all columns in the query, while the From Selection option indicates that you only want to take examples from a selected column.

For this illustration, the From Selection option is selected since the sample data has only one column.

When you choose the type of column example you want to use, the Add Columns from Examples pane will open. Here, you can enter one or more sample values for the new column.

Power Query will then analyze the sample values and suggest a transformation that will create the new column. You can then review the suggested transformation and make any necessary changes before clicking OK.

In the above image, you can see that Power Query has rightly guessed the type of transformation being carried out and named the output column accordingly. It’s also suggested the M query that can be used to output the result. This is all from just entering a few values in the new column.

When you click the OK button, the new column is added to the table, and you can simply delete the old column if you don’t need it anymore.

Convert Negative Numbers to Positive Numbers with the Custom Column Option

The Custom Column feature in Power Query is another powerful and versatile tool that enables you to create new columns in your data using custom formulas. This tool allows you to manipulate and transform your data in a precise and tailored manner using the M query or any logical expression.

To use the Custom Column tool to convert negative numbers to positive numbers, in the Add Column menu, click on Custom Column.

When the Custom Column window opens, follow these steps.

1. Enter a descriptive name for the new column you intend to create using the New column name field. This name will appear as the column header for the new column that will be created.
2. Craft the Power Query M formula expression or logic for generating values in the new column using the Custom column formula box. You can utilize various functions, operators, and references to existing columns to construct the desired transformation.
3. Once the formula is complete, check the bottom left. A statement will appear to tell you whether the syntax is wrong or not. Once you’ve validated the syntax, click the OK button to apply. Here, the syntax simply multiplies the existing column by -1.

The new column will be added to the table, and the syntax will convert the negative numbers in the first column to positive numbers.

Convert Negative Numbers to Positive Numbers with the Advanced Editor

The Advanced Editor serves as a powerful tool that allows you to directly edit the underlying code of your data transformations.

It provides a comprehensive view of the M language, enabling you to create complex and customized data manipulations beyond the capabilities of what’s obtained on the graphical interface.

To access the Advanced Editor, go to the View tab on the Power Query ribbon and select Advanced Editor.

Also, you can find the Advanced Editor tool in the Home tab.

Irrespective of the menu tab you use, a separate window will open displaying the code generated by the visual transformations you’ve applied when you click on Advanced Editor.

What you want to do is add a new syntax to the existing one. But, before adding a new syntax, here are a few things that will help your understanding of the M code syntax.

• The Let keyword is used to define local variables within an expression. It is useful for creating more complex expressions that are easier to read and understand.
• Every line of code in the Let expression except the last one must end with a comma. This helps to indicate the end of that line of code and the beginning of a new one. When a line of code doesn’t end with a comma, it signifies the end of the Let expression.
• The In keyword defines the scope of a local variable, limiting its usage to the expression that comes after it. By specifying this expression, you ensure that variables are evaluated within the Let expression where they have been defined. This prevents any unintended usage of variables beyond their intended scope.

#"Add Column"= Table.AddColumn(#"Removed Columns", "Absolute Value", each Number.Abs([Value]), type number).

The syntax above adds a new column named Absolute Value to the existing query, changing the negative in the Values column to positive numbers.

Table.AddColumn(table as table, newColumnName as text, columnGenerator as function, optional columnType as nullable type)

Table.AddColumn – this function is used to add a new column to an existing table. It uses four arguments. The first argument, #”Removed Columns”, represents the variable holding the table where the new column will be added.

“Absolute Value”, the second argument, specifies the name of the new column to be created. The third argument, each Number.Abs([Value]), defines the values to be populated in the new column.

The each keyword indicates the Number.Abs([Value]) expression will be applied to each row in the Value column. The Number.Abs() function calculates the absolute value of the values in the Value column.

type number, thelast argument in the Table.AddColumn function specifies the data type of the new column – in this case, it signifies a numeric data type.

When you add the syntax and verify that there are no errors, click on the Done button. A new column will be added to the table afterwards.

Convert Negative Numbers to Positive Numbers with DAX

Data Analysis Expressions (DAX) provides a powerful and flexible set of functions, operators, and constants that enables you to perform complex calculations, aggregations, and data transformations directly within their data models.

In this section, you will see how you can use DAX to convert negative numbers to positive numbers using calculated columns and measures.

Convert Negative Numbers to Positive Numbers with Calculated Columns

Calculated columns are a valuable feature that allows you to add new data to existing tables in your Power BI data model. Unlike measures, which are summarized values for visualizations, calculated columns store individual values for each row in a table.

This makes them useful for performing calculations, transformations, or data manipulations that are specific to each row.

To use a calculated column to turn negative numbers to positive, go to the Data view, select the table containing the negative numbers and click on New column under Table tools.

Positive Value = [Value] * -1

When you enter the above syntax into the formula bar, Power BI will add a new column to the table with positive numbers.

One drawback of using this approach is that it increases the size of your model because you cannot delete the redundant column, unlike in Power Query.

Convert Negative Numbers to Positive Numbers with Measures

Measures return an aggregate value which can be filtered accordingly. So, when you convert the negative numbers in a column into positive numbers, you won’t see them appear as columns in your table.

Using measures is the most efficient method to convert numbers from negative to positive. That is because with measures, you’re not increasing the size of your model, and you don’t have to change the shape of the dataset.

You can simply create a measure that returns the positive aggregate of the values in the column containing the negative values and reference the measure when you want to use it in other measures.

Additionally, you don’t need a complex measure to convert negative numbers to positive numbers.

ABS ( SUM ( 'Table'[Value] ) )

-1 * SUM ( 'Table'[Value] )

The first expression uses the ABS function. ABS is a mathematical DAX function that returns the absolute value of a number. It takes only a number as an argument. The SUM function returns a single value, hence why it can be passed as an argument in the ABS function.

The other expression simply multiplies the output of the SUM expression by -1. This turns the values into positive numbers.

Conclusions

Transforming negative numbers into positive numbers is an essential data transformation that forms the bedrock of reliable analysis. Power Query provides effortless tools like Column from Examples, Custom Column, and Advanced Editor for changing the sign of numbers in your data.

DAX also offers a convenient way to convert negative numbers to positive numbers through calculated columns and measures.

Essentially, this shows that Power BI provides you with a wealth of options to transform your data into the ideal format for further analysis.