Power Query is one of the best features of Excel, it’s also highly flexible giving users several different ways to add new columns. We’ll explain the different methods and when to use each one to add columns with static text, or custom formulas.
The most popular ways to add new columns in Excel Power Query involve using custom formulas, creating conditional columns that return values based on conditional logic, and using AI to create formulas for you based on text input examples.
We’ll provide an overview of the different methods.
Let’s jump in!
Table of Contents
What is Excel Power Query?
Power Query is a subset of features in Microsoft Excel, and some of Microsoft’s other business intelligence tools designed to quickly transform large amounts of data. Data is imported into Power Query, and worked with at a column level, similar to how you would work with a database.
Each data transformation step is recorded, and applied to data when you click refresh. This allows you to update the incoming data and apply the same pre-recorded steps allowing users to automate repetitive tasks and save time.
Launching the Power Query Editor
To launch the Power Query Editor from Excel, navigate to the Data, Get & Transform Data section of the Excel Ribbon. You can either Get Data to connect Excel to an external data source, or you can select From Table / Range to work with data that’s already inside your Excel workbook.
After selecting data to work with a new window appears that launches the Power Query Editor that will look like the screenshot below.
The top Ribbon of the Power Query editor lists all of the different functions you can perform, and each step is tracked on the right side of the screen where you can view, edit, and modify the steps that have previously been applied.
Once transformations are completed, you can click on “Close & Load” to apply the steps to the data in your Excel workbook.
How to Add Columns in Excel Power Query
To add new columns in Power Query, launch the Power Query Editor, then navigate to the Add Column section of the Power Query Ribbon. On the left side, there are buttons to add columns from Example, add Custom Columns using formulas, create Conditional Columns along with several other options.
Depending on your selection, you will have different options available for how the new column is added.
- Column From Examples – Uses AI to generate formulas based on examples of desired output.
- Custom Column – Creates new columns using Power Query M formulas.
- Invoke Custom Function – Executes a previously saved formula with the ability to utilize parameters.
- Conditional Column – Adds columns based on conditional IF logic.
- Index Column – Adds a new column starting at 0 or 1 and fills the row down in chosen incriments.
- Duplicate Column – Equivalent of Copying and Pasting a column with the same values.
Other options within this toolbar include the ability to perform different math or date functions, along with adjust the formatting of columns, or parse a single column into multiple columns.
Adding New Columns in Power Query Using Custom Formulas
To add a new column in Excel Power Query using formula, navigate to Add Column, Custom Column from the Power Query editor. A formula box will appear where you can type in formulas based on names of existing columns. Formulas use the Power Query M language and can range from simple to incredibly complex calculations.
The screenshot below shows the Custom Column formula dialog box. You can name the new column here and type your formula in the box below it. The right side of the dialog box gives you the ability to double click on a column name to insert it into a formula.
Formulas are applied to an entire column and automatically filled down. Column names are referenced in square brackets and the typical operators, such as plus signs, minus signs, and multiplication work. The required formula language is different than standard Excel formulas which will not work within Power Query.
When working with text it should be surrounded by quotes. Numbers do not require quotes around them.
For example, to create a column with set text the formula would be = “Desired Text”
Adding New Columns in Power Query Using Examples
The Columns from Examples feature of Power Query allows users to generate a new column by providing an example of the desired output. Power Query uses the examples of desired output to suggest formulas, select a result from the dropdown box and a formula will automatically be created without having to know Power Query M.
In the screenshot below we created a column from example using the Date Column as the selected column. A new column on the far right of the dataset is created where we began typing in the day number. Power Query suggests different formulas in the dropdown box where we can select “Day from Date” to let Excel automatically generate the required fomrula.
Columns from Example are incredibly useful and highly recommended if you are new to Power Query. It can take some time to learn many of the common Power Query formulas for data transformation, but by letting Excel generate the formulas for you it’s much easier to get started.
Tip: The exact formula being generated is shown above the data columns. You can also use the formula as a started point and further edit it manually as required.
Adding Conditional Columns in Excel Power Query
To add columns based on the values of another column in Power Query, navigate to the Add Columns section and select Conditional Column. This launches the Conditional Column Editor where users can leverage a template to create IF statements that can return static values or values from another column when conditions are met.
The Add Conditional Column screen has a section at the top to name the new column, and the IF statement is generated behind the scenes when you fill out different operators, values, and desired output. Values and Output can either be text, numbers, or values from other columns.
The Else section at the bottom is the value to return when none of the conditions above are met.
Note: In Power Query, users define the lack of data as a null. Nulls are special within the data world because they represent the absence of information. It’s the equivalent of creating an empty cell or empty column in an Excel workbook.
Power Query for Excel gives users a number of different ways to add columns to their dataset. They can be added using custom formulas, using the Power Query M language which differs from the standard Excel format. They can also be added by providing example output which eliminates much of the need to write your own custom formulas. When a column needs to be added based on IF statement style logic, a conditional column can be created.
With so many different options, and the extra powerful capabilities of Power Query M formulas, you can create a column for just about anything while taking advantage of Power Query’s ability to handle large amounts of data efficiently and store repeatable processes.