Add A Custom Column (Power Query) - Microsoft Support

Skip to main content Sign in with Microsoft Sign in or create an account. Hello, Select a different account. You have multiple accounts Choose the account you want to sign in with. Applies ToExcel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2024 Excel 2021 Excel 2019 Excel 2016

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. 

Add a custom column

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

2. Select Add Column > Custom Column. the Custom Column dialog box appears.

3. Enter a new column name.

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

5. Select OK. 

6. Once you add a custom column, make sure it has an appropriate data type. If you see the Any  icon to the left of the column header, change the data type to what you want. For more information, see Add or change data types.

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. 

Common example formulas

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

Power Query for Excel Help

Add a custom column (docs.com)

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

Add an index column (docs.com)

Need more help?

Want more options?

DiscoverCommunity

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Microsoft 365 subscription benefits

Microsoft 365 training

Microsoft security

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Ask the Microsoft Community

Microsoft Tech Community

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Yes No Thank you! Any more feedback for Microsoft? Can you help us improve? (Send feedback to Microsoft so we can help.) What affected your experience? Resolved my issue Clear instructions Easy to follow No jargon Pictures helped Other Didn't match my screen Incorrect instructions Too technical Not enough information Not enough pictures Other Any additional feedback? (Optional) Submit feedback By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×