Microsoft Power Query – Number Column

Leave a Comment / Excel / By

Hi All,

In this blog-post, we would understand the group “From Number” in Microsoft Power Query. In our previous blog-post, we had discussed the split number and Text function in Microsoft Power Query.

One question which is most likely asked by the people that when we can do the calculation in Ms-Excel, then why we need to learn Microsoft Power Query? and the reasons are listed below-

  1. 1-Because Microsoft Power Query can upload heavy data without any interruption.
  2. 2- No additional calculative formulas are required to do the calculation.
  3. 3- The best place to extract and combine the “text values”. Find and remove the duplicates values in easy steps.
  4. 4- it can merge the multiple files at once. it can append the different types of file format too.

There are so many reasons to learn Microsoft Power Query. We have already understood some of the reason in our previous blog-post and Today we will cover the Form Number in Microsoft Power Query. Let’s take an example and understand.

We have a sales database above in the screenshot.

How to convert the database into the table format-

  1. Select the database
  2. Go to Insert Tab->> Table or Press Short cut key Ctrl+T
  3. Make sure click on “My table has a header”
  4. Ok

Once the database converts into a table format, Now let’s upload the database into Power Query.

How to upload Table into the power query?

  1. Go to Data Tab
  2. Into the group of “Get & Transform Data”
  3. Click on Table\Range option

  1. After click on Table/Range, the database will be open in a new dialogue box. This window is called “Microsoft Power Query” and the page where it will perform the task is called “Microsoft Power Query Editor”.
  2. Microsoft Power Query Editor
  3. Ribbon Tab
  4. M-Code (Natural programming language)
  5. Table database
  6. Microsoft Power query settings
  7. Applied Steps

In the above screenshot, the date column is showing date with time format, however, we want only date format. So, Let’s change it-

How to do the correct Date Format?

  1. Select the Date Column
  2. Home Tab
  3. In the group of “Transform”
  4. Click on Date Type
  5. Choose Date

Or

Click on the date icon located at the left edge of the Date Column and choose Date option.

When we choose the date format, we get a dialogue box. Which ask us, whether we want to see the impact on the existing column or place the data on the new column. Refer the screenshot below-

For this example, we will choose to replace current data. Let’s click on the option “Replace current” and see the impact on below mention screenshot-

By the way, we came here to discuss the Number columns. Therefore, we will select the sales amount. When we click on the ribbon tab and find out the From Number group. Form Number, group option is showing into two tabs. Transform & Add column. Let’s understand the difference between the first-

  1. Transform- when we do any calculation and wanted to see its impact on the existing column. We will use option Transform
  2. Add column— when we do any calculation and wanted the result in a new column. We will use the option Add Column

 

The number is divided into 6 parts. Let’s discuss each one by one—

From Number

Statistics-

Before applying any condition, make sure, we have selected the columns which are consisting of numerical values otherwise theses option will be hidden.

When we click on the statistics option. The following list appears related to statistical tools-

  1. Sum– it will show the total sales amount.  follow the steps- Select the sales amount>>Go to transform>> Click on statistics>> choose Sum

And this will be the answer-

Similarly, if we choose Maximum, Minimum, average, median and standard Deviation option we will get the result according to their selection. However, in the last 2 option “Count Values” and “Count Distinct values”, by the way, their sounds look the same, but the reality is different. come and Let’s understand it one by one–

  1. Count Values– This option helps us to count the entry entered the selected column, no matter if it has duplicates values. It counts everyone. For example, if we select the column “Sales Rep” and apply the option >> Transform>>Statistics>>Count Values. Answer is 15

 2. Count Distinct Values- when we want to count only unique values use Distinct count. For example, if we select the sales rep. and apply Transform>>Statistics>>distinct count. The answer will be 10 because it refuses to count duplicate values-

As we can see into the above screenshot when we click on the option “Standard”. The following list will appear –

  1. Add– when we choose the option “Add”. A new window appears which ask to enter a number to add each value in the column. For Example, if we want to add 1000 in each value. Follow the below mention procedure-

Select the sales amount>>standard>>add>>1000>>Ok

As we did and explained, how we Add numerical value into the above screenshot. Similarly, we can do the different calculation without any further formula. Other options are listed below-

  1. Multiply- Enter a number by which multiply each value in the columns
  2. Subtract- Enter a number to subtract from each value in the columns
  3. Divide- Enter a number by which divide each value into the columns
  4. Integer-Divide- Enter a number by which integer-Divide each value in the columns
  5. Modulo- Enter a number from which to find a reminder to each value in the columns
  6. Percentage- Enter a percentage to apply to each value in the columns
  7. Percent Of- Enter a value to find each value in the selected columns as a percentage of it

Apart from adding and subtraction, but, also we can do the scientific calculation into the power query. When we click on the option “scientific”. The following option will appear-

  1. Absolute value– when we want to change all negative value into the positive number
  2. Power– when we want to calculate the square or cube of the number column
  3. Square root– when we want to get the square root of numerical values.

Similarly, we can use the option exponent, logarithm and factorial. Just select the column (Sales amount)>> click on scientific >> and choose the option

If we want to do trigonometry calculation, following trigonometry formula, we can use these function to do the calculation –

  1. Sine
  2. Cosine
  3. Tangent
  4. Arcsine
  5. Arccosine
  6. Arctangent

Select the column>> Trigonometry>>choose the option >> ok

Rounding-

The round function rounds a number to a specified number of digits. If Cell B1 contains 23,45789 and we want to round that value in two decimal places. We can use this formula-

  1. Round-
    1. Select the column
    2. Rounding
    3. Round
    4. Specify how many decimal places to round to
      • If we type 2. it will convert into 2 decimal places
      • If we type 1. It will convert into 1 decimal place
      • If we type 0. It will convert the value into the round figure

2. Round-Up- it always rounds a number up.

3. Round down- it always rounds a number down.

Information-

As its name information, it gives us the information about the numerical values. When we click on the information following list will be display-

  1. Is Even- It gives us TRUE if the value is even number or it gives us FALSE value
  2. Is ODD- It gives us TRUE values if it is finding an odd number and for others, it gives FALSE
  3. Sign- For positive numerical value, it shows the 1 sign and for the numerical value. It shows -1

Hope, this blog-post helps you to understand the group “From Number” into the Power Query. Do apply on your sheet and let us know if you have any queries. Do write your queries and question into the comment box. We will be happy to assist you.

 

Stay tuned with Nurture Tech Academy.

Happy Learning 😊

Leave a Comment

Your email address will not be published. Required fields are marked *