Power BI Custom column adds a new column in data model based on the existing columns in data model. You can create a custom column in Power Query Editor using M Queries.
When a custom column is created, Power BI Desktop adds it as an Applied Step in the Query Settings of the query, you can rename, modified custom column at any time.
Lets see how to create a custom column in Power BI.
As you can see, here we have a customer date.
copy sample data:
| Id | First Name | Last Name | Reg. Date | OrderDate | DeliveryDate |
| 1 | Ajay | Singh | 31/01/2021 | 31/01/2021 | 05/02/2021 |
| 2 | Mohit | Raina | 12/10/2021 | 31/01/2021 | 01/02/2021 |
| 3 | Sujit | Singh | 03/09/2021 | 31/01/2021 | 10/02/2021 |
| 4 | Amit | Agarwal | 31/01/2021 | 31/01/2021 | 03/02/2021 |
| 5 | Roman | Saini | 31/01/2021 | 01/02/2021 | 10/02/2021 |
| 6 | Manoj | Kumar | 03/09/2021 | 03/02/2021 | 11/02/2021 |
| 7 | Amit | Divedi | 03/09/2021 | 07/04/2021 | 08/04/2021 |
| 8 | Suraj | Kumar | 31/01/2021 | 07/04/2021 | 08/04/2021 |
| 9 | Mayank | Rawat | 31/01/2021 | 07/04/2021 | 17/04/2021 |
| 10 | Ajay | Kumar | 03/09/2021 | 07/04/2021 | 09/04/2021 |
| 11 | Rohit | Negi | 03/09/2021 | 07/04/2021 | 15/04/2021 |
Now click on Transformation Data tab to the Power Query Editor.
After that, click on Custom column under Add column tab.
Once you click on Custom column tab, a Custom column window opens as shown below.
You can see the list of available columns on the right, these are the columns that you have already in your data model. You can create a custom columns using these existing columns also.
Now we will add a new column in data model that will have a full name.
Lets give it a name as CC_FullName, so basically it will be a concatenation of two columns FirstName and LastName.
Now to add an existing column into formula box, just select the FirstName column from available list and then click on << Insert button or you can also add a column by double-clicking it in the list.
Once you done with this, It adds FirstName column into Formula box.
After that, Lets add a LastName column in similar way but before that add ampersand and double quote & ” ” & symbol that will concatenate FirstName with LastName and add space between FirstName and LastName.
= [First Name] & " " & [Last Name]
Once you write the M query formula and If there are no errors, you will see a green check mark and the message No syntax errors have been detected as shown in above screenshot.
In case, If there are a syntax error, you will see a yellow warning icon along with a link to where the error occurred in your formula as shown below.
After that click on OK button.
Once you click on OK button, Power BI Desktop adds your custom column to the data model also adds the Added Custom step to your query’s Applied Steps list in Query Settings.
You can see, a custom column named CC_FullName is added into data model which is having full name.
If you don not want to select column from Available columns list then you can simply write your formula in formula box also.
It will suggest you available column names, or predefined M queries functions automatically as shown below.
Lets create one more custom column to get the aging, a date difference in days between Delivery date and Order date.
Once you click on Custom Column, a Custom Column window opens.
Now give it a name as CC_Aging(DeliveryDate – Order Date).
To get the date difference in days there is a M query function Duration.Days.
Now in Formula box, start typing as Duration, and you will see it auto suggests you a list of available columns and M queries functions based on your query, now select Duration.Days from lists as shown below.
Now complete the formula as shown below.
= Duration.Days([DeliveryDate] - [OrderDate])
Now click on OK button, and you can see a newly custom column is added to the data model and adds the Added Custom 1 step to your query’s Applied Steps list in Query Settings.
You can see, it displays the aging a date difference between Delivery date and Order date in days.
Modifying custom column
In case, if you want to modify, rename custom column. Just go to Applied Steps under query setting, then identify custom column and double click on that.
Here we have double click on custom column Added Custom1.
Once you double click on custom column, you will see a same custom column window opens again. Here you can rename or modify you custom column’s formula.
Lets rename it as CC_Aging then click on OK button.
Once you click on OK button, you will see a column name is renamed as CC_Aging.
After that, click on Close & Apply tab to save your changes.
You can also see the custom columns information and modify them through Advance Editor as well.
In case, if you have multiple data model, first select data model to which you have added a custom column then click on Advance Editor.
Now you can see, a CustomerData window opens which is basically a your model name.
And you can see a custom column information there. You can rename, or modify custom column here also.
Also Read..
