NOTE: This post was created using the November 2014 version of Power Query. In later versions, some of the screens or functionality could be different.

An apology: I want start this post by apologizing for making it harder to follow this series than I should have. Rather than put the onus on you, dear reader, to maintain the working file for each post, I should have just supplied the necessary source file at the beginning of each post. I have hereby rectified that on all previous posts in this series and will provide the work file for each post from here on.

You can download the Excel 2013 file necessary to perform the steps in this post here. To get to the existing query, click the Workbook Queries button on the Power Query ribbon.

While I tend not to do much with number columns in Power Query other than formatting, I wanted to make sure to provide an example to show that you certainly CAN work with them. There are certainly occasions where doing math in Power Query makes sense. I have just not encountered many yet.

We start the the query shown in Figure 1.

Figure 1

Our sole number column in this query is the Awardees column, shown highlighted in Figure 1. This fields contains the number of people that shared each portion of a particular Nobel Prize. We will start out with something basic.

Information

The Information button, in the Number Column section of the Transform tab and the From Number section of the Add Column tab, has three options. The options are shown in Figure 2.

Figure 2

The Is Even option returns the value TRUE if the number is an even number and FALSE if the number is odd. The Is Odd option does the opposite, returning TRUE for odd numbers and FALSE for even ones. Contrary to what you might think, the Sign option is not related to Tesla OR the “Wormsign” cry from Dune. It actually returns a –1 for negative numbers and a 1 for positive numbers.

Our first example will be to use the Sign option. In order to make it worthwhile, we will first use the Replace Values transformation to make some of our values negative.

1. With the Awardees column highlighted, click the Replace Button on the Home tab of the ribbon, shown in Figure 3. Note: The Replace Values button on the Transform tab will work just fine as well.

Figure 3

This will activate the Replace Values dialog, as shown in Figure 4.

Figure 4

2. Enter 1 in the Value To Find and –8675309 in the Replace With boxes, respectively, as shown in
Figure 4. Thanks to Jenny for the number.

3. Click OK. The result will resemble Figure 5.

Figure 5

Notice the presence of –8675309 values where the 1 values used to be. Note also that doing this causes the Awardees column to change to the Any data type. Interesting. Even though the new value is still a whole number, the use of an Any Column transformation will convert that column to the Any data type. You can see this in Figure 6.

Figure 6

4. Making sure the Awardees column is selected, click the Data Type option and change it to Whole Number.

5. Click to select the Awardees column again.

6. Click the Sign option of the Information button on the Add Column tab of the ribbon, as shown in Figure 7.

Figure 7

Since we chose the Add Column tab from which to perform this action, the result will be a new column containing the results of the Sign transformation, as shown in Figure 8.

Figure 8

Notice that all of the rows with a negative value for Awardees have a –1 value for the Sign column.

Since we don’t want to keep the work from this example, we will delete these steps to return to our starting point.

7. Delete the last three steps in the APPLIED STEPS, as shown in Figure 9.

Figure 9

Like Math and Stuff

For this example, we will use the values in the Awardees column to calculate the percentage of the Nobel Prize won by each Laureate. Take a close look at Figure 10.

Figure 10

For the 1903 Nobel Prize for Physics, we see there are three Laureates. However, at first glance, the values seem a bit confusing. A little research cleared that up, though. Antoine Henri Becquerel won 1/2 of that prize while Pierre Curie and Marie Curie split the other half, getting 1/4 each. Interesting, eh? The More You Know.

This means we can use the value in the Awardees field to calculate what percentage of each Nobel Prize was awarded to each Laureate.

8. Click the Add Custom Column button on the Add Column tab of the ribbon, as shown in
Figure 11.

Figure 11

This will launch the dialog shown in Figure 12.

Figure 12

9. Name the new column something sensible, like Award Percentage.

10. Type “1/” in the Custom Column Formula box.

11. Scroll down in the Available Columns box until Awardees becomes visible.

12. Click on Awardees to select it.

13. Click the Insert button, shown by the arrow in Figure 13.

Figure 13

Once you have completed these steps, the Add Custom Column dialog should resemble Figure 14.

Figure 14

The formula is 1/[Awardees]. This means that, for each row, this new column will be equal to the result of dividing the value 1 by the value in the Awardees column in that row.

14. Click OK. The result is shown in Figure 15.

Figure 15

As we would expect, the values make sense as Mr. Becquerel has 50% and the Curies each have 25%. Grand.

The Award Percentage column defaults to the Any data type. Let’s make it something more appropriate.

15. Click the Award Percentage column to select it.

16. Click the Data Type option on either the Home or Transform tabs. The Transform tab option is shown in Figure 16.

Figure 16

17. Select Decimal Number as the data type.

The final query for this example will resemble Figure 17.

Figure 17

If you are following along with your own file, you can click Close & Load to save this query and then save the file for next time. Otherwise, you can just use the source file I will provide at the beginning of that next post, in which we will play with Date Column options.