Add a column based on a data type (Power Query)

When you add a column, there are many common ways to change and format different data types. For example, you may 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 achieve what you want and without you 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.

The data type groups on the Add Column ribbon tab

Important The following examples use the Add Column tab so the original column remains unchanged. This is generally a best practice that can help avoid inadvertent refresh errors. Each of these commands are also available under the Transform tab which changes the original column. Be cautious when changing the original column which may lead to inadvertent refresh errors.

Tip To quickly create sample data and tryout a procedure, create an Excel table of several columns and rows, and then import it. For more information, see Create a table and Import from an Excel Table.

This section provides several text examples and then shows a list of all the text commands available. Using the Parse command is discussed in another topic. For more information, see Parse text as JSON or XML.

To capitalize each word in a string:

Select the column, LoudMemo.
Select Add Column > Extract > Capitalize Each Word. A new column is added with the result.

Adding a column to capitalize each word

To find the length of a string:

Select the column, Full Name.
Select Add Column > Format > Length. A new column is added with the result.

Adding a column to get the length of a word

Summary of text data type commands

Extract	Format	Parse
Length First Characters Last Characters Range Text before Delimiter Text after Delimiter Text between Delimiters	lowercase UPPERCASE Capitalize Each Word Trim Clean Add Prefix Add Suffix	XML JSON

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.

Adding a column to to get the factorial of a number

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.

Adding a column to find the tangent of a number

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.

Adding a column to add two numbers from two columns

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.

Adding a column to get a percentage of a number

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 > Percentage The Excel Format Number as Percent icon which displays 80%. For more information, see Format numbers as percentages.

Summary of number data type commands

Standard	Statistics	Scientific	Trigonometry	Rounding
Add Multiply Subtract Divide Divide (Integer) Modulo	Sum Minimum Maximum Median Average Standard Deviation Count Values Count Distinct Values	Absolute Value Power > Square Power > Cube Power > Power Square Root Exponent Logarithm Base-10 Logarithm Natural Factorial	Sine Cosine Tangent Arccosine Arcsine Arctangent	Round Up Round Down Round

This section provides several date, time, and duration examples and then 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.

Adding a column to get the month name of a date

To display the week of the year number of a date:

Select the column, Order Date.
Select Add Column > Date > Week of Year.

Adding a column to get the week number of a date

To display the current age of a person given their date of birth:

Select the column, DOB.
Select Add Column > From 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 is the number of days from the date of birth to the present.
Select Add Column > Duration > Years. This converts the Age column value to the number years or 68.97984786.
To remove the decimal places, select Add Column > Rounding > Round Down.

Adding a column to get someone's age based on their DOB

Summary of date, time, and duration commands

Date	Time	Duration
Age Date Only Parse Year > Year Year > Start of Year Year > End of Year Month > Month Month > Start of Month Month > End of Month Month > Days in Month Month > Name of Month Quarter > Quarter Of Year Quarter > Start of Quarter Quarter > End of Quarter Week > Week of Year Week > Week of Month Week > Start of Week Week > End of Week Day > Day Day > Day of Week Day > Day of Year Day > Start of Day Day > End of Day Day > Name of Day Subtract Days Combine Date and Time Earliest Latest	Time Only Local Time Parse Hour > Hour Hour > Start of hour Hour > end of hour Minute Second Subtract Combine Date and Time Earliest Latest	Hours Minutes Seconds Total Years Total Days Total Hours Total Minutes Total Seconds Subtract Multiply Divide Statistics > Sum Statistics > Minimum Statistics > Maximum Statistics > Median Statistics > Average