Number Transformation in Power Query Editor

Number transformation in Power Query help in modifying numerical data to ensure it’s accurate and ready for analysis, such as changing data types, performing calculations, rounding numbers, and handling missing values. Power Query Editor in Power BI provides an intuitive interface to perform these tasks efficiently. In this blog post, we’ll explore various techniques to transform numerical data in Power Query Editor, helping you make your data cleaner and more useful for your reports and visualizations.

Common Steps for Number Transformation in Power Query Editor:

• Statistical
• Standard
• Scientific and Trigonometry
• Rounding Numbers
• Information Tools

Standard Calculation using Number Transformation in Power Query Editor

We can perform Addition, Multiplication, Subtraction, Division Operations between columns using standard calculation in Number Transformation.

Steps for Statistics functions:

• To add a constant value to a column
• Select the column, go to the ‘Transform’ tab 
• Use the ‘Standard’ option to select ‘Add

Similarly, when you choose options like Maximum, Minimum, Average, Median, and Standard Deviation, Power Query Editor will calculate and display the results based on your selection. For the last two options, “Count Values” will give you the total number of entries in the selected column, while “Count Distinct Values” will provide the number of unique entries in that column.

Example:

Statistical Functions

Statistics Functions allow you to evaluate basic stats for a selected column (sum, min/max, average, count, count distinct, etc.) in Power Query Editor.

Rounding Numbers:

The rounding function in Power Query Editor allows you to round a number to a specified number of decimal places. For example, if you have a value of 23.45789 in a column and you want to round it to two decimal places, you can easily achieve this by following these steps: