Power Query: Deep Dive into Power Query's "From Number" Section in Add Column

Raw data often contains numerical fields that aren't ready for analysis. They might need a simple calculation, a quick rounding, or a change to their sign. While you could write complex formulas in a custom column to handle these tasks, Power Query provides a powerful, user-friendly alternative: the From Number section of the Add Column tab. This suite of tools automates common numerical transformations, allowing you to clean, prepare, and enrich your data with just a few clicks.

This guide will walk you through the key features of the From Number section, demonstrating how each one simplifies your data workflow and ensures your numerical data is accurate and ready for reporting.

Data transformation interface in Power Query Editor, displaying options for mathematical and statistical functions, with a list of countries and query steps applied.

The Essentials of Power Query: Deep Dive into Power Query's "From Number" Section in Add Column

This group of tools is your calculator within Power Query: Deep Dive into Power Query's "From Number" Section in Add Column. It allows you to perform basic arithmetic operations on a number column, either by referencing another column or by using a static value. This is perfect for when you need to quickly adjust values or create new calculated fields without writing any code.

• Add, Subtract, Multiply, Divide: These functions do exactly what their names suggest. A common use case for these is to calculate gross profit by subtracting costs from revenue, or to apply a discount percentage by multiplying a price column. For example, if you want to calculate the price after a 10% discount, you would select the Price column, go to Add Column > From Number > Standard > Multiply, and enter 0.9 as the value.

• Modulo: This operation returns the remainder after a division. It's a surprisingly useful tool. For example, you can use it to determine if a value is even or odd by finding the remainder when divided by 2. If the result is 0, the number is even; if it’s 1, it's odd.

• Percent Of: This is a quick way to find what percentage of a number a given value is. For instance, if you want to see what percentage of the total budget each department's spend represents, you can use this function.

Handling the Finer Points: "Scientific" and "Rounding"

Not all numbers are simple integers. The "Scientific" and "Rounding" groups are essential for more advanced or specific numerical tasks, especially when dealing with financials or scientific data.

• Absolute Value: This function returns the absolute value of a number, turning any negative value into a positive one. This is crucial when you need to measure the magnitude of a value regardless of its sign, such as calculating the total variance in a budget report.

• Power: This function raises a number to a specified power. It's particularly useful for calculating exponential growth or for complex statistical models.

• Square Root: You can quickly find the square root of a number in a selected column.

• Round Up, Round Down, Round: These rounding functions are indispensable for ensuring consistency in financial or statistical reports. Instead of dealing with long decimal values like 125.7562, you can quickly round them to 126 (round up), 125 (round down), or a specific number of decimal places, making your data much cleaner and easier to read.

Unlocking Deeper Insights: The "Information" Section

The "Information" section provides simple, one-click functions that give you quick insights into your numerical data. While these might seem basic, they are incredibly useful for building conditional logic and for performing quick data audits.

• Sign: This function returns a 1 if the number is positive, -1 if it's negative, and 0 if it's zero. This is a powerful preliminary step for creating a conditional column that, for example, assigns a "Profit" or "Loss" label to your data.

• Is Even / Is Odd: As their names suggest, these functions create a new column with a TRUE or FALSE value, indicating whether the number in the selected column is even or odd. This is helpful for segmenting data or for auditing purposes.

• Is In Range: This function allows you to quickly check if a number falls within a specified range, returning TRUE or FALSE. This is a quick and effective way to identify outliers or to filter your data.

By mastering the From Number section, you gain the ability to perform a wide range of transformations on your numerical data quickly and efficiently. It saves you from writing repetitive M code and ensures that your numbers are in the perfect format for accurate analysis and reporting.