Introduction

Transforming existing columns and creating new ones are essential skills in Power BI. In this hands-on tutorial, we’ll explore how to efficiently add custom columns in both Power Query Editor and the Power BI interface. Since this topic is relevant for the PL-300 exam, this guide will be a valuable resource on your way passing the exam.

💡 What are Custom columns?

Custom columns are user-defined fields that allow you to add new data based on existing columns. They enable advanced data transformations, calculations, and enrichments, making it easier to meet specific analytical requirements.

There are two primary ways to create custom columns in Power BI:

  1. 🛠️ Using Power Query Editor
    Create custom columns and apply transformations to your data before it’s loaded into Power BI using the Power Query M language.
  2. 💻 Using Power BI interface
    Create custom columns and apply transformations directly in the data model using Data Analysis Expressions (DAX).
💡
Which way is better?
Power Query is generally better for performance because transformations are applied before data is loaded into the model, reducing resource consumption.
DAX is more flexible for dynamic calculations but can slow performance if used excessively on large datasets.

🛠️ Add custom column in the Power Query Editor

Let’s create a new column using Power Query. We want to create a new column named major_code, based on the column major with the following rule:

major major_code
Data Science ➡️ 1
Data Engineering ➡️ 2
Data Analytics ➡️ 3

In order to do this, open Power Query Editor. Navigate to the Add Column tab and click Custom Column.

Enter the following Power Query M formula to assign the values based on the column major and click OK.

if [major] = "Data Science" then 1 
else if [major] = "Data Engineering" then 2 
else if [major] = "Data Analytics" then 3 
else null

Once applied, the column contains the following values:

💻 Add custom column in the Power BI interface

Now, let’s create another column using DAX. We want to create a new column named role, based on the column major_code with the following rule:

You can view this post with the tier: Academy Membership

Join academy now to read the post and get access to the full library of premium posts for academy members only.

Join Academy Already have an account? Sign In