In Power BI Desktop, you can add a Calculated Column in Data and Report View or a Custom Column in Power Query Editor to add new data to a table already in your model.

Regardless, you will add a DAX Calculated Column in Data View or a Computed Column in Power Query, you will note that the outcome result will be the same. However, in some cases like Import Mode and huge Model size, the performance and execution time may differ.

In this post, we will explain What are the main differences between Custom Column Vs Calculated Column in Power BI?, and Which one you should use to add a new column in your Model? and finally, you will learn How to create a Calculated Column and Computed Column in Power BI Desktop.

Main differences between Custom Column Vs Calculated Column in Power BI

As we have earlier mentioned, both techniques (Custom Column and Calculated Column) result in the same outcome as shown below.

But adding a new column in Power BI mainly depends on many aspects such as:

• USAGE,
• Model Size,
• Cardinality,
• Type of connections (DirectQuery or Import Mode),
• The complexity of the formula calculations,
• Number of relationships,
• Number of distinct values,
• Data Types…etc.

So if you have multiple ways to add a column and your model is not really huge or you are using DirectQuery mode, it actually doesn’t matter, and you should consider using the approach that best fits your SKILLS and the simple way that achieve your goals in a faster and easy manner.

However, it’s also recommend when you add a new column to your model in Power BI to think about the following:

• Try first to create your new column in the source query by adding the calculation to a View in your database, creating a column using SQL language is an efficient way because it would make the data source do the calculations for you.
• If you can’t add your new column in your View in Database, so you should think FIRST to create a custom column rather than a calculated column.
• If you can’t apply your requirements with one of the above mechanisms, so you have to use DAX calculated column.

Note: In a DirectQuery, the DAX calculated columns and Power Query computed columns have approximately the same evaluated query time.

Custom Column Vs Calculated Column in Power BI

Now, let’s go deeply explain What’re the main differences between Custom Column Vs Calculated Column in Power BI and Which technique should you use when you have the options to create a new column in Power Query Editor (Custom Column) or in Report View (Calculated Column).

1. Custom Column in Power BI.
2. Calculated Column in Power BI.

1) Custom Column in Power BI

In this section, we will explain all considerations that you should know before creating a new Custom Column in Power BI, also you will learn when you should create a Custom Column vs Calculated Column, and finally, you will learn how to create a Custom Column in Power BI?

What’s Custom Column in Power BI?

A Custom Column is a kind of column that you can create in Power Query Editor in Power BI to add a new column that didn’t originally exist in the data source.

Create Custom Column Consideration

Before creating a new Custom Column in Power Query Editor, you should first be aware of the following:

• When you add a Custom Column in Power Query Editor, you have to use M language.
• Creating a new Custom Column or even performing bit changes on the currently created Custom Column requires more time depends on the table size because it needs a full refresh of the table.
• The Custom Column does compress, which makes the .pbix file smaller, and the performance usually faster.
• The size of the Custom Column in Power Query is always smaller than the created Calculated Column in DAX.
• You must have good knowledge of M language to can write a formula in a Custom Column in Power Query Editor.
• You can’t use DAX expressions in a Custom Column in Power Query Editor.

It’s strongly recommended to read the main differences between DAX Vs M Language.

When to use Custom Column in Power Query?

• If you want to prepare your data and perform data transformation before the data loaded into the model in a more compact and optimal way, you should use Custom Column in Power Query.
• Use Custom Column, If your model is really huge.
• If your scenario requires adding multiple calculated or computed columns depends on other columns on the same row, consider using Custom Column.
• Use Custom Column, If you need to create a new column as a part of your query and you need to load values into your new column from a data source.

In Import Mode, It’s recommended to add computed columns in Power Query, whenever possible, because it preprocesses the data before it loaded into the model in a more compact and optimal way.

How to create a Custom Column in Power Query Editor in Power BI?

To create a new column as a part of your query and load values into your new column from a data source, you have to create a Custom Column in Power Query Editor by doing the following:

1. Open your Dashboard in Power BI Desktop.
2. In the “Home” tab, click on Transform Data.

1. The Power Query Editor should be opened now to transform and prepare your data.
2. Below Queries, select your table that you would like to add your new column.

3. Click on the “Add Column” tab, then click on Custom Column.

4. The Custom column dialog would be opened to create a computed column.
5. Once you finish, and there is no syntax error has been detected, click on OK.

6. The new custom column will be added as a new step in the Applied steps as shown below.

7. Now, at the Home tab, Click on Close and Apply to apply your changes.

8. In the Fields pane, you can now see your new custom column has been listed under the selected table. but unlike the calculated column, it doesn’t have any special icon.

---

2) Calculated Column in Power BI

In this section, we will explain all considerations that you should know before creating a new Calculated Column in Power BI, also you will learn when you should create a Calculated Column vs Custom Column, and finally, you will learn how to create a Calculated Column in Power BI?

What’s Custom Column in Power BI?

A Calculated Column is a kind of column that you can create in Report View or Data View in Power BI to add a new column that didn’t originally exist in the data source.

Create Calculated Column Considerations

Before creating a new Calculated Column in Power Query Editor, you should first be aware of the following:

• When you add a Calculated Column in Data View or Report View, you have to use DAX.
• In some cases, the DAX calculated column may increase the model storage and consume extra memory depends on the usage, the model size, the number of relationships, cardinality, and other aspects.
• Creating a new DAX Calculated Column is faster because it does not require a full refresh of the table, unlike the new Computed Column that requires a full refresh of the table.
• Unlike Custom Column, the DAX Calculated Column does not compress.
• The size of the DAX Calculated Column is always bigger than the created Custom Column in Power Query. (Actually, it doesn’t matter, the most important is the size of the entire model).
• It is not considered an optimum practice to use DAX for calculations if you can use Custom Column.
• Avoid using a Calculated Column if you want to use one of the iterator functions such as SUMX, COUNTX, MINX.
• You must have good knowledge of DAX to can write a formula in a Calculated Column in Report or Data View.
• You can’t use M language in a Calculated Column in Report or Data View in Power BI.

It’s strongly recommended to read the main differences between DAX Vs M Language.

When to use DAX Calculated Column in Power Query?

• If you want to perform in-depth data analysis on the top of the Model, you should use DAX in a Calculated Column in Data View or Report View.
• Use the Calculated column, If you need to perform DAX analysis after the data is loaded to your model.
• If you need to aggregate rows from other tables, consider using a DAX Calculated Column.
• If your formula calculation is very complex to be done in a Custom Column, so it’s better to use DAX in a Calculated Column.
• Use the Calculated column, If you can’t use Custom Column.

In Import Mode, the DAX calculated column may consume extra memory and increase the model storage, this is because the calculated column needs to be recalculated when its formula depends on the refreshed tables.

How to create a Calculated Column in Power BI?

To add new data to a table already loaded in your model, you have to create a calculated column by doing the following:

1. Open your Dashboard in Power BI Desktop.
2. In the Fields pane, ensure that the table that you need to add your new calculated column is selected.
3. In Report View > Click on Modeling > Click on New Column to create a new calculated column.
4. The formula bar should be shown now to build your calculation using DAX.

4. Once you write your DAX formula, Click on the TRUE icon to check the syntax and save your column.
5. If everything is OK, the new calculated column will be appear and listed in the Fields pane with a special icon below the selected table as shown below.

Besides the Report View, you can alternatively create a new Calculated Column from Data View in Power BI Desktop as below.

A calculated column can be added to any table in your model, and it must return a scalar or single value.

---

Conclusion

In conclusion, we have explained What are the main differences between Custom Column Vs Calculated Column in Power BI and which mechanisms should you use when creating a new column in Power BI.

See Also

• Main differences between DAX Vs M Language
• Choose a technique to add a column.
• How to merge two tables in Power Query Editor
• Power BI calculated column based on Slicer