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.
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.
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.
Summary of text data type commands
Extract |
Format |
Parse |
---|---|---|
|
|
|
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 > Percentage which displays 80%. For more information, see Format numbers as percentages.
Summary of number data type commands
Standard |
Statistics |
Scientific |
Trigonometry |
Rounding |
---|---|---|---|---|
|
|
|
|
|
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.
To display the week of the year number of a date:
-
Select the column, Order Date.
-
Select Add Column > Date > Week of Year.
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.
Summary of date, time, and duration commands
Date |
Time |
Duration |
---|---|---|
|
|
|