Ultimate Guide to Adding Columns Using Excel Power Query

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.

Adding columns in Power Query for Excel is fast and easy with multiple options ranging from formulas for static text, math operations, conditional logic, and AI examples.

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!

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.

Launching the Power Query Editor in Excel using data from a table / range

After selecting data to work with a new window appears that launches the Power Query Editor that will look like the screenshot below.

Screenshot of the Power Query editor in Excel

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.

Screenshot of the Add Column section of the Power Query Editor

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.

Screenshot of the Custom Column button in Excel Power Query

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.

Example of creating a column that multiplies values in two other columns together to highlight the Power Query M syntax

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.

Screenshot of the Column from Examples button in Excel Power Query Ribbon

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.

Example of creating a custom column from example using the Power Query editor

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.

Screenshot of the conditional column button in Power Query Ribbon

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.

Example of creating a conditional column using an IF statement to return different values based on the values of a column in Excel Power Query

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.

Conclusion

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.

Scroll to Top