In Power Query, use the M function Number.Abs to return a number’s absolute value: for example, Number.Abs(-25) returns 25. In the Power Query Editor, you can also select a numeric column and choose Transform > Number Column > Standard > Absolute Value to apply the operation to the whole column.
What absolute value means in Power Query
Absolute value is a number’s distance from zero, without regard to whether the number is positive or negative. It is never negative: the absolute value of -8 is 8, the absolute value of 8 is also 8, and the absolute value of zero is zero. In Power Query, the function that performs this operation is Number.Abs, part of the M language used to define queries.
This is useful when a negative sign indicates direction or accounting presentation but you need to compare the size of values. For example, a change of -14 and a change of 14 have the same magnitude, even though they represent opposite directions. Taking the absolute value preserves that magnitude while discarding the sign.
Absolute value does not mean “make every value positive” in the sense of correcting its underlying meaning. Once you replace a signed value with its absolute value, you can no longer tell from that result whether the original was above or below zero. If direction matters, retain the original column or add a separate result column.
Apply absolute value from the Power Query ribbon
For a quick transformation of an existing numeric column, use the built-in command in the Editor. In Excel for Microsoft 365 on Windows, open the query, select the numeric column, and choose Transform > Number Column > Standard > Absolute Value. Power Query adds a transformation step to the query; the change is applied again whenever the query refreshes.
- In Excel, select a cell in the table or range, then choose Data > From Table/Range to open it in Power Query. If the query already exists, open it from Data > Queries & Connections.
- Select the column heading for the values you want to transform. Confirm that its data type is numeric, such as Whole Number, Decimal Number, or Fixed Decimal Number.
- Choose Transform > Number Column > Standard > Absolute Value.
- Check several rows in the preview, then choose Home > Close & Load to return the result to Excel.
The ribbon operation transforms the selected column rather than creating a second column. If the original signed values must remain available, use an added column instead. Ribbon labels can vary slightly between Power Query hosts and updates, but the M operation behind the transformation is Number.Abs.
Create a separate absolute-value column
To preserve the source values, add a custom column. In the Power Query Editor, choose Add Column > Custom Column, provide a name such as Absolute Amount, and enter an M expression in the formula box. The expression does not start with an Excel-style equals sign.
For a table whose numeric field is named Amount, enter Number.Abs([Amount]). For a row containing -42.5 in Amount, the new column returns 42.5, while the original Amount column remains -42.5. The bracketed field reference means “the value in this row’s Amount column.”
For example, if a transaction table has Date, Amount, and Category columns, adding Number.Abs([Amount]) gives you a magnitude column without changing dates, categories, or signed amounts. This is often the safer choice for analysis because you can still distinguish a refund from a charge or a decrease from an increase.
In a query’s M code, the equivalent step is commonly expressed with Table.AddColumn. Assuming the previous step is named Source and contains an Amount column, this expression adds a new column and returns 42.5 for an input of -42.5:
Table.AddColumn(Source, "Absolute Amount", each Number.Abs([Amount]), type number)
Power Query’s interface can create this step for you, so you do not need to edit M manually for an ordinary transformation. The explicit output type in the example indicates that the added values are numeric.
Data types, nulls, and errors
Number.Abs is for numeric values. If the column contains text that looks like a number, such as "-42", the function does not automatically treat every text value as a number. First convert the column to a numeric type, and inspect the result for conversion errors. In the Editor, select the column and use the data type control in the column heading or the options on the Transform tab.
Text-to-number conversion can depend on regional conventions. For instance, a decimal comma and a decimal point are not interpreted identically in every locale. If the source uses a different convention from your current settings, use Change Type > Using Locale (available from the column’s data type menu) and select the appropriate type and locale. Converting correctly before taking absolute values is important: an error during conversion cannot be fixed by applying Number.Abs.
A null represents a missing or unknown value, not the number zero. The nullable form of the function preserves null as null, so missing data does not become a fabricated zero. Errors in a row likewise remain errors unless you handle them separately. This distinction matters when aggregating results: a zero is a real numeric observation, whereas null may mean that no value was supplied.
Check the data type icon beside the column name and scan the preview for error indicators after conversion or transformation. If a column mixes numbers, text, and blanks, decide how each case should be treated before loading the result. Replacing missing values with zero or removing errors changes the data and should reflect the meaning of the source, rather than being an automatic cleanup step.
Useful examples and when to keep the sign
Absolute values are useful for calculations about size, but not every question is about size alone. Common uses include:
- Variance magnitude: Find how far actual results are from a target, regardless of whether they are over or under it.
- Distance between readings: Compare the size of a measurement difference when direction is not relevant to the comparison.
- Amounts with mixed signs: Examine the size of debits and credits while retaining the signed source field for accounting interpretation.
- Threshold checks: Identify values whose difference from a reference is large, whether the difference is positive or negative.
For example, suppose a Variance column contains -3, 0, and 5. Applying Number.Abs([Variance]) in an added column produces 3, 0, and 5. That output lets you rank rows by magnitude, but it does not show which rows were below the target. Keep both columns if you need to report magnitude and direction together.
A common misconception is that absolute value is the same as removing a minus sign from displayed text. It is a numeric operation, not a text cleanup rule. A value stored as text may need conversion first, and a minus sign embedded in an unusual text format may require additional parsing. Similarly, if the business rule is “convert refunds to sales” or “treat all entries as positive,” confirm that discarding the sign is appropriate; absolute value alone does not explain what the sign meant.
Power Query Number.Abs versus Excel ABS
Excel worksheet formulas and Power Query transformations serve different stages of a workflow. In a worksheet, Excel’s ABS function calculates a result in a cell and recalculates as the worksheet changes. In Power Query, Number.Abs is an M function that transforms data during query refresh, before the results are loaded to a worksheet or data model.
The names differ because the functions belong to different languages: ABS is an Excel worksheet function, while Number.Abs is written in M. A worksheet formula such as =ABS(A2) is not the expression to enter in Power Query’s Custom Column box. There, use a row expression such as Number.Abs([Amount]), with Amount replaced by the actual column name.
Choose Power Query when the transformation should be repeatable as part of importing and cleaning source data. Choose a worksheet formula when the calculation belongs in the grid and should respond directly to worksheet edits. The two approaches can produce the same numeric result, but they run at different points and have different refresh and editing behavior.
Refresh behavior and troubleshooting
A Power Query step is part of a sequence of transformations. Its position matters: if you apply absolute value before changing text into numbers, the operation may fail; if you apply it after filtering rows, it only affects the rows that remain at that point in the query. Use the Applied Steps pane to review the order and select earlier steps to inspect the data at each stage.
If the command is unavailable or creates errors, check the following:
- Wrong type: Confirm that the selected column is numeric, not text or a date.
- Mixed values: Look for text entries, conversion errors, or unexpected symbols in a mostly numeric column.
- Wrong field reference: In a custom column, match the name inside square brackets to the exact column heading.
- Lost direction: If negative and positive values need different treatment later, add a column instead of transforming the original.
- Unexpected output: Check the step order and data type, then refresh the query to confirm the transformation is being applied to the current source.
Power Query stores the transformation instructions, not merely the preview currently on screen. When the source changes and you refresh, the same absolute-value step runs on the new rows. That repeatability is useful, but it also means a new text value or a changed source format can introduce errors on a later refresh. Review refresh results when a source is maintained by another system or team.
Frequently Asked Questions
What is the Power Query function for absolute value?
Use Number.Abs. For a numeric column named Amount, the custom-column expression is Number.Abs([Amount]).
How do I make a number positive in Power Query?
Apply Transform > Number Column > Standard > Absolute Value to a numeric column, or use Number.Abs in a custom column. This removes the sign mathematically; it does not preserve whether the original value was negative.
Does Number.Abs change null to zero?
No. A null remains null, because it represents a missing value rather than a numeric zero. Decide separately how missing values should be handled in your data.
Can I use Number.Abs on a text column?
Not reliably as text. Convert values to a numeric type first and deal with conversion errors or locale-specific number formats before applying the function.
How can I keep the original negative values?
Add a custom column with Number.Abs([YourColumn]) instead of transforming the existing column. Replace YourColumn with the real field name; the new column holds magnitudes while the source retains its signs.
Related
- Excel Print Shortcut: How to Print a Worksheet
- Excel Number Format Shortcut: Quick Keys and Examples
- Excel New Comment Shortcut: Windows, Mac, and Notes
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- PowerPoint Transitions: How to Add and Customize ThemTo add a transition in PowerPoint, select a slide, open the Transitions tab, choose an effect, and preview…
- Excel Power Query and Business Intelligence ExplainedYes—Power Query in Excel is a data preparation tool commonly used in business intelligence (BI). It imports, cleans,…
- Access Text to Columns: Split Data in a FieldMicrosoft Access does not have an existing-table command named Text to Columns like Excel does. To split a…
- Conditional Formatting Greyed Out? Excel FixesIf Conditional Formatting is greyed out in Excel for Microsoft 365 on Windows, first check whether you are…
- How to Alphabetize in Google Sheets Without Mixing RowsTo alphabetize data in Google Sheets, select the full range you want to sort, then choose Data >…
- Print Excel Sheets With Gridlines: Settings and FixesYes. In Excel for Microsoft 365 on Windows, select the worksheet, then choose Page Layout > Sheet Options…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.