When you add a column, you can change and format different data types in many common ways. For example, you might want to determine if a number is odd or even, lowercase a text string, or display the month name of a date/time. Power Query offers many useful commands to help you achieve your goal without resorting to formulas.
Each of the following sections shows a few examples of what you can do, and then summarizes all the commands available for each data type.
Important
The following examples use the Add Column tab so the original column remains unchanged. This approach generally helps avoid inadvertent refresh errors. Each of these commands is also available under the Transform tab, which changes the original column. Be cautious when changing the original column, as it might lead to inadvertent refresh errors.
Tip
To quickly create sample data and try out a procedure, create an Excel table of several columns and rows, and then import it. For more information, see Create and format tables and Import from an Excel Table.
Add a column from text
This section provides several text examples and then shows a list of all the text commands available. Another article discusses using the Parse command. For more information, see Parse text as JSON or XML (Power Query).
To capitalize each word in a string:
Select the column, LoudMemo.
Select Add Column > Format > Capitalize Each Word. A new column is added with the result.
To find the length of a string:
Select the column, Full Name.
Select Add Column > Extract > Length. A new column is added with the result.
Summary of text data type commands
| Extract | Format | Parse |
|---|---|---|
|
|
|
Add a column from a number
This section provides several number examples and then shows a list of all the number commands available.
To calculate the factorial of a number:
Select the column, Sample Number.
Select Add Column > Scientific > Factorial. A new column is added with the result.
To find the tangent of a number:
Select the column, Given Number.
Select Add Column > Trigonometry > Tangent. A new column, named Tangent, is added with the result of 0.999999673.
Select Add Column > Rounding > Round Up. A new column, named Round Up, is added with the result of 1.
Note
The number 0.785398 is 270 degrees in radians.
To add two columns of numbers:
Select the columns, StoreA and StoreB.
Select Add Column > Statistics > Sum.
To calculate a percentage of a number:
In this example, the value of 4 is a survey rating in the range of 1 to 5.
Select the column, Customer rating.
Select Add Column > Standard > Percent Of. In the Percent Of dialog box, enter a value or use a column value.
In our example, enter 500. You want to express the percentage as 80% because 4 is 80% of 5 in a rating scale of 1 to 5. 80% is .08 as a decimal value where the scale is 0 to 1 and 100% is a decimal value of 1. To convert to the correct percentage and scale, the calculation is: (4/500)*100 which yields a value of .08.
Tip
After you load the query to a worksheet, the Percent Of column displays .08, but the ultimate goal is to express as a percentage. In Excel, select the Percent Of column, and then select Home > Data Type > Percentage which displays 80%. For more information, see Format numbers as percentages.
Summary of number data type commands
| Standard | Statistics | Scientific | Trigonometry | Rounding |
|---|---|---|---|---|
|
|
|
|
|
Add a column from a date and time
This section provides several date, time, and duration examples. It also shows a list of all the date, time, and duration commands available.
To display the month name of a date:
Select the column, StartDate.
Select Add Column > Date > Month > Name of Month.
To display the week of the year number of a date:
Select the column, Order Date.
Select Add Column > Date > Week > Week of Year.
To display the current age of a person given their date of birth:
Select the column, DOB.
Select Add Column > Date > Age. This command converts the value to the length of time format of: Days.Hours:Minutes:Seconds or 25177.15:27:41.1876935. This value represents the number of days from the date of birth to the present.
Select Add Column > Duration > Total Years. This command converts the Age column value to the number of years, such as 68.97984786.
To remove the decimal places, select Add Column > Rounding > Round Down.
Summary of date, time, and duration commands
| Date | Time | Duration |
|---|---|---|
|
|
|
See also
Add a column from an example (Power Query)
Add a custom column (Power Query)