You can add a custom column to your current query by creating a formula. Power Query validates the formula syntax in the same way as the Query Editing dialog box. For more information about the Power Query Formula Language, see Create Power Query formulas.
-
To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, edit, and load a query in Excel.
-
Select Add Column > Custom Column. the Custom Column dialog box appears.
-
Enter a new column name.
-
Insert a column into the Custom Column Formula box by selecting a column from the Available Columns list, and then selecting Insert.Note You can reference multiple columns as long as you separate them with an operator. For example, to calculate a TotalSales column, you add Total and SalesTax using the formula = each [Total] + [SalesTax].
-
Select OK.
-
Once you add a custom column, make sure it has an appropriate data type. If you see the Any
Tip You can try another approach to get the results you want. Use a custom column to merge values from two or more columns into a single custom column. For more information, see Merge columns.
The following table summarizes common examples of custom formulas.
|
Formula |
Description |
|---|---|
|
"abc" |
Creates a column with the text abc in all rows. |
|
1+1 |
Creates a column with the result of 1 + 1 (2) in all rows. |
|
[UnitPrice] * [Quantity] |
Creates a column with the result of multiplying two table columns. |
|
[UnitPrice] * (1 – [Discount]) * [Quantity] |
Calculates the total price, considering the Discount column. |
|
"Hello" & [Name] |
Combines Hello with the contents of the Name column in a new column. |
|
Date.DayOfWeekName([DOB]) |
Creates a new column that displays a weekday name, such as Monday, derived from a DOB Date/Time column data type. |
|
DateTime.Time([DOB]) |
Creates a new column that displays just the time derived from a DOB Date/Time column data type. |
|
Date.Month([DOB]) |
Creates a new column that displays the month as a number from 1 to 12, such as 4 for April, derived from a DOB Date/Time column data type. |
See Also
Add a custom column (docs.com)
