Monday, December 27, 2021

Add a column based on a data type (Power Query) [MS]

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:

  1. Select the column, LoudMemo.

  2. 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:

  1. Select the column, Full Name.

  2. 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




  • Length

  • First Characters

  • Last Characters

  • Range

  • Text before Delimiter

  • Text after Delimiter

  • Text between Delimiters

  • lowercase


  • 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:

  1. Select the column, Sample Number.

  2. 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:

  1. Select the column, Given Number.

  2. Select Add Column > Trigonometry > Tangent. A new column, named Tangent, is added with the result of 0.999999673.

  3. 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:

  1. Select the columns, StoreA and StoreB.

  2. 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.

  1. Select the column, Customer rating.

  2. 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






  • 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:

  1. Select the column, StartDate.

  2. 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:

  1. Select the column, Order Date.

  2. 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:

  1. Select the column, DOB.

  2. 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.

  3. Select Add Column > Duration > Years. This converts the Age column value to the number years or 68.97984786.

  4. 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




  • 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

Example article

Power Query for Excel Help

Add a column from an example

Add a custom column

Add a conditional column

Add or change data types

No comments:

Post a Comment

Search This Blog

Change or update your email password [MS]

Outlook Get Started Set up accounts ...