How to Add a Column in Power BI Query Editor

Adding a new column in Power BI's Power Query Editor is one of the most fundamental skills for cleaning and shaping your data. It allows you to create new information from existing fields, build calculations, and prepare your dataset for visualization. I'll show you several straightforward methods for adding columns, from writing simple formulas to using conditional logic and even having Power BI build the column for you based on examples.

What is the Power Query Editor?

Before jumping into the "how-to," let's quickly clarify what we're working with. The Power Query Editor is a data transformation and preparation tool inside Power BI. Think of it as your workshop for getting data ready before it gets into your Power BI report. It’s here that you clean up messy data, combine tables, and, as we'll cover, add new columns to make your final reports more meaningful.

Adding columns at this stage is powerful because the transformation is applied every time you refresh your data, ensuring your new calculated fields are always up-to-date and consistent without any manual work later on.

Launching the Power Query Editor

First things first, you need to open the tool. You can access the Power Query Editor from the main Power BI Desktop interface.

• On the Home ribbon, find the Queries section.
• Click the Transform data button. This will launch a new window: the Power Query Editor.

Once you're in the editor, you'll see your data loaded in a spreadsheet-like view. Now, you're ready to start adding columns.

Method 1: Add a "Custom Column" with a Formula (M Language)

The most direct way to add a column is by creating a "Custom Column" and defining your own formula using Power Query's M language. Don't worry, for most common tasks, the formulas are quite simple and intuitive, often resembling basic Excel functions.

Let's walk through it with an example. Imagine you have a table of product sales with columns for [Quantity] and [UnitPrice], and you want to calculate the total revenue for each line item.

Step-by-Step Guide:

1. Navigate to the Add Column tab on the Power Query ribbon.
2. Click on the Custom Column icon.
3. A dialog box will appear. Here, you'll configure your new column:
4. For our example, the formula to calculate revenue would be:

Simply type that into the formula box. Power Query will tell you "No syntax errors have been detected." at the bottom if your formula is correct.

1. Click OK. Your new TotalRevenue column will appear at the right end of your table.

Bonus Example: Combining Text Fields

This method isn’t just for numbers. You can also use it to combine, or concatenate, text. Say you have [FirstName] and [LastName] columns and want to create a [FullName] column.

• Start another Custom Column.
• Name it "FullName".
• Use the following formula to combine the names with a space in between:

Finally, once a new column is added, check its data type. Power Query often guesses correctly, but it's good practice to make sure. Click the icon on the column header (like "ABC," "123," or "1.2") and select the correct type (e.g., Decimal Number, Whole Number, Text) to ensure your calculations and filters work properly in your report.

Method 2: Create a "Conditional Column" with Logic

What if you need to categorize your data based on certain rules? This is where the "Conditional Column" feature shines. It provides a user-friendly interface to build if-then-else logic without writing a single line of code.

Let’s say you want to categorize sales transactions from our previous example into "Large," "Medium," and "Small" buckets based on the TotalRevenue we just calculated.

Step-by-Step Guide:

1. On the Add Column tab, click Conditional Column.
2. The Conditional Column dialog box is intuitive:
3. Here’s how you'd set up the logic:
4. Click OK. Power Query will evaluate each row in your TotalRevenue column and populate the new SalesCategory column with the corresponding value.

The Conditional Column tool is incredibly useful for creating segments, status flags, and any other kind of business rule-based categorization.

Method 3: Add a "Column From Examples"

This is easily one of the most powerful and time-saving features in the Power Query Editor. Instead of writing a formula, you simply provide Power BI with an example of what you want the output to look like, and it automatically writes the underlying transformation formula for you.

Let's use an example where you have an OrderDate column (e.g., "2023-10-27") and you want to create a new column formatted as Year-Month (e.g., "2023-Oct").

Step-by-Step Guide:

1. Navigate to the Add Column tab and click Column From Examples. A new, blank column will appear at the right end of your table with the header "Column1".
2. Click into the first cell of this new column.
3. Look at your OrderDate for that row. If it's "27/10/2023," type the desired output into the new cell manually: 2023-Oct.
4. Press Enter. Power Query will analyze what you did and try to apply the same logic to the rest of the rows. Sometimes it gets it right on the first try. Other times, it might need another example or two.
5. If the initial suggestions aren't quite right, scroll down to a row where the logic didn't work and provide a second correct example. Power BI's algorithm will refine its suggested formula.
6. Once you're happy with the preview, check the M language formula Power BI generated for you in the bar at the top, give the column a proper name, and click OK.

This "Column From Examples" feature is brilliant for complex text manipulations, like extracting parts of a string or reformatting dates, where writing the formula from scratch might be tricky.

Other Quick and Useful Column Additions

The "Add Column" tab includes other handy one-click options you should know about:

• Index Column: A simple way to add a numbered column to your table. You can have it start from 0, 1, or even a custom number and increment size. It's great for giving your rows a unique ID if one doesn’t already exist.
• Duplicate Column: As the name suggests, this creates an exact copy of a selected column. This is useful when you want to perform a transformation (like splitting text or changing the data type) while still keeping the original column for reference.
• Date & Time Actions: If you select a date or datetime column, a range of new options become available. You can easily extract the Year, Month, Day, Quarter, Week of Year, Day of Week, and more, each into its own new column with just a single click.
• Text Actions: Similarly, selecting a text column unlocks actions under the "From Text" section. You can extract, parse, or format text - for instance, creating a column with the Length of the text, converting it to UPPERCASE, or Trimming leading/trailing spaces.

Final Thoughts

These methods cover nearly every scenario you'll encounter for adding columns in Power Query. Whether you're doing simple math, building business logic, or extracting snippets of data, mastering these tools is essential for effective data preparation and getting your data into shape for building impressive reports in Power BI.

As you build more complex reports, you'll find much of your time is spent in the data prep stage, much like we did here. This is why we created Graphed. Instead of manually navigating menus to create custom columns or joining sources, we let you simply say what you want in plain English, like "Show me my sales revenue by product category" or "Compare my campaign ad spend to Shopify sales." Graphed instantly builds live dashboards from all your marketing and sales sources without you ever opening a query editor. It saves you the hours spent preparing data so you can focus on the insights themselves.