Future-Proof Your Career, Master Data Skills + AI

Blog

Future-Proof Your Career, Master Data Skills + AI
Blog Home

Blog

Custom Columns In Power BI Vs. Conditional Columns

by | 7:00 pm EDT | April 28, 2022 | Power BI

In this blog post, we’ll talk about the difference between Conditional and Custom Columns in Power BI. We’ll also show examples of when and how we can use them, including some of their alternatives. 

Custom columns require a different formula language that we need to learn first. On the other hand, conditional columns are easy to create, require no formulas, and are powerful due to the extensive filtering options available. 

Creating Custom Columns In Power BI

First, select any table where you want to add a custom column. For this example, we’re adding this custom column inside our Currencies table.

To create a custom column, click the Custom Column option which is located within the Add Column ribbon. 

custom columns in Power BI

A popup box will appear where we can create a column by implementing some logic inside. For this example, we named our custom column as “Full Exchange Rate”.

custom columns in Power BI

Inside the Custom column formula, we typed “NZD/” &, then chose the Currency Types under the Available columns.

custom columns in Power BI

To apply this custom column, we need to click the OK button.

custom columns in Power BI

A new column (Full Exchange Rate) is now created on our Currencies table.

custom columns in Power BI

This is one way of creating a custom column. However, I personally don’t use this feature because it requires a different formula language that doesn’t relate to Excel, M language, or DAX. We need to learn this new formula language first before we could write these custom columns.

I want to recommend an easier way before using the Custom Column option. But there are still some rare occurrences where we might want to use it.

There are other ways that we can do to exactly get the result that we have created, which is easier than writing it with a formula. One of these is the Column From Examples where it will automatically add a new column. We can populate its contents from a current selection or provide an input based on a selected column.

custom columns in Power BI

Using Index Columns In Power BI

There are instances where we have to create an Index column on our table. These are usually used on databases to easily locate data without having to search each of their rows. For Power BI data, we can use it as a slicer. 

We can easily create an Index column by clicking the Index Column option under the Add column ribbon.

custom columns in Power BI

Directly clicking on it will provide us an Index column that starts with 0.

custom columns in Power BI

We can change that by clicking its dropdown arrow and selecting the From 1 option.

custom columns in Power BI

Instead of 0, our Index column will start from 1 by using that option.

custom columns in Power BI

We also have the Custom option if we want to start our index on another number instead of 0 or 1.

custom columns in Power BI

For example, we want to start our index number to 5. We just need to set that on the Starting Index field. We can also create a custom increment if we want to. For example, we want to increment by 2.

custom columns in Power BI

It will then provide another Index column that starts with 5 and increments by 2. 

custom columns in Power BI

However, what if we wanted a custom Index column where the USD is number 1, euro is number 2, pound is number 3, and AUD is number 4? That’s where the Conditional Column option comes in.

Using Conditional Columns In Power BI

Using the Conditional Column is an interesting way to create columns. To use this, just click on this option located within the Add Column ribbon.

custom columns in Power BI

We can then create an If logic inside our tables. In this part, we’re creating a new column named as Ccy index and a condition that says If the Currency Types is equals to USD, we want the output to be 1.

custom columns in Power BI

We can then add the other rules or clauses that we want by clicking the Add Clause button.

Then add the other rules for the other currencies, and click the OK button to create this column.

When creating columns that we’ll use on our model, we should always set their data type properly. We can change that by clicking the data type icon on their left side. 

For this example, our custom Index column should have a Whole Number data type.

We now have a custom Index column where its index number depends on the currencies. By having this custom Index column, we can sort our Currency Types column based on it.

***** Related Links *****
M Code In Power Query Custom Columns | Power BI
How To Unpivot Columns In Power BI
Unpivot Multiple Columns Dynamically In Power BI

Conclusion

All in all, these are the different ways of creating custom columns in Power BI. The main point here is that we hardly ever use the Custom Column feature because it requires a different formula language that doesn’t relate to Excel, M language, or DAX.

We need to learn this new formula language first before we could write these custom columns. Therefore, using the Conditional Column to create a custom column would be better. 

All the best,

Sam

Related Posts