All about Add columns in Power BI

In today's blog, we will deep dive into the Power query editor. The interface of the power query editor is a replica of an excel spreadsheet but in the former things are much easier as compared to excel. Power query editor offers a lot of features which we have discussed in the guide to power query editor blog. 

In this blog, we will focus on one of the features i.e. add columns. You can find it next to the transform tab in the power query editor. We will be working with the sample superstore data. The idea is to explore the add column tab.

Let's start with duplicating the column which is just a click away feature available in the power query editor. You just need to select the column of which you need to make a duplicate and select the duplicate column tab available on the top. Unlike in Excel, you need to apply shortcuts to achieve the same. The power query editor is all about making your life easier. From duplicating the columns we will move to index the columns which can be useful at times just to provide a unique identifier to different rows. You need to select the column with index and a toggle will open which will ask you to select the index whether it starts from 0 or 1 or else you need custom indexes. I am not attaching the screenshots for these because it is quite basic and I believe you will find your way out if you follow the guidelines.

Let's move to custom columns where you can create columns with some formulas associated with them. I am trying to extract the year from the order date. You need to select the custom column tab available on the top it will open a new toggle. You need to apply the formula as shown in the picture. It is quite similar to text to columns in excel with different delimiters. To extract the year we will be using the date to text formula.

Pro tip - After applying this you will get a new column that will have all the years but focus on the data type since the column contains only the year we will be converting the data type into whole numbers. 

Let's move further and explore more options available in add column. If you are familiar with the sample superstore data you can find many negative values in the profit column. In that case, I would like to add another column which will state whether we are making profits or not. If you want to do a similar thing in excel then the answer would be a nested ifs statement which can be tricky at times. In power query editor you don't need to worry about the formula. Let me show you the magic!!!

The idea is to highlight which of the product is profitable and which one is not. To achieve that we created a conditional column if the profit is greater than 0 then only it will be called profitable and in all the rest scenarios it is unprofitable. And you can add more cases if you want.

Isn't that simple? I will show you some more magic let's move to columns from examples. I am looking to extract the first two alphabets from order id. To start off we will select the order id column then we will select the column with the example tab available on the top. Under the column with the example, you need to select from a selection. A new column is added where you just need to type the first 2 alphabets for the first 2 rows and see the magic.

I love this about the power query editor. You can play around with different features and it won't be that difficult to comprehend. For more articles and pro tips do follow our blog.

Thanks for Reading  Let's connect on  LinkedIn. For more such blogs do follow us.