Create new column from existing column in Power BI is very useful when we want to perform a particular analysis. Many times the new column requires clipping a string part from an existing column.
Such a situation I faced a few days back and was looking for a solution. And this is when I again realised the power of “Power Query“. This article is to share the same trick with you.
If you are new to Power BI, then I would suggest going through this article for a quick idea of its free version, Power BI desktop. It has numerous feature for data manipulation, transformation, visualization, report preparation etc.
Here are some popular application of Power BI as a Business Intelligence tool.
- Data modelling in Power BI
- Explicit and Implicit measures of Power BI
- Joining tables in Power BI
- Map visualization in Power BI
- Data extraction from websites with Power BI
This article covers another super useful feature of Power BI. Adding a new column derived from the existing columns are very common in data analytics. It may be required as an intermediate step of data analysis or fetching some specific information.
For example, we may need only the month or day from the date column, or only the user-id from the email-id list etc.
In this article I will demonstrate the process using the data sets related to India’s state-wise crop production and rainfall data.
Lets start the process step by step and see how I have done this.
Use of “Add Column” and “Transform” options
Power BI desktop offers two options for extracting information from an existing column. Both the options namely “Add column” and “Transform” has different purposes altogether.
Add column option adds a new column extracting the desired information from the selected column. Whereas the Transform option replaces the existing information with the extracted text.
Here our purpose is to create new column from existing column in Power BI. So let’s explore the “Add Column” feature and the options it offers.
Create new column from existing column Power BI with “Add column” option
First of all, you need to open the “Power Query Editor” by clicking “Transform data" from the Power BI desktop. Here you will get the option “Extract” under the “Add column” tab as shown in the below images.
Extracting the “Length“
This option is to fetch the length of any particular string and populate the new column. See in the below example, I have created a new column with the length of the state names from “State_Name” column.
The power query associated with this feature is as given below. The M language is very easy to understand. You can make necessary changes here itself if you want.
= Table.AddColumn(#"Changed Type", "Length", each Text.Length([State_Name]), Int64.Type)Extracting the “First Characters“
If we select the “First Character” option, then as the name suggests, it extracts as many characters as we want from the start. As shown in the below image, upon clicking the option, a new window appears asking the number of characters you want to keep from the first.
As a result, the new column named “First Characters” contains the first few characters of our choice.
Extracting “Last Characters“
In the same way, we can extract the last characters as we need. See the below image, as we select the option "Last Characters", again it will prompt for the number of characters we wish to fetch from the last of the selected column.
As we have provided 7 in the window asking the number of characters, it has extracted a matching number of characters and populated the column “Last Characters”.
Extract “Text Range“
This option offers to select strings from exact location you want. You can select starting index and number of characters from the starting index. See the bellow example where I wished to extract only the string “Nicobar”.
Keeping this in mind, I have provided 12 as the starting index and 7 as the number of characters from the 12th character. The result is the column “Text Range” has been populated with the string “Nicobar” as we wanted.
Extracting using delimiters
Another very useful feature is using delimiters for extracting necessary information. It has again three options for using delimiters, which are:
- Text Before Delimiter
- Text After Delimiter
- Text Between Delimiter
The image below demonstrates the use of the first two options. As the column “State_Name” has only one delimiter i.e. the blank space between the words, so in both the cases, I have used this delimiter only.
You can clearly observe differences between the outputs here.
The script for executing this operation is given below.
= Table.AddColumn(#"Removed Columns", "Text After Delimiter", each Text.AfterDelimiter([State_Name], " "), type text)Below is the example where we need to extract text between the delimiters. The process is same as before.
The code is as below.
= Table.AddColumn(#"Inserted Text After Delimiter", "Text Between Delimiters", each Text.BetweenDelimiters([State_Name], " ", " "), type text)Another example with different delimiters
Below is another example where you have some delimiter other than the blank space. For example one of the columns in the data table has a range of years. And the years separated with a “-“.
Now if we use this in both cases of text before and text after delimiter, the results are as in the below image.
Use of “Conditional Column“
This is another powerful feature of the Power BI desktop. Where we create a new column applying some conditions to another column.
For example, in the case of the agricultural data, the crop cover of different states have different percentages. And I wish to create 6 classes using these percentage values.
First, open the Power Query Editor and click the “Conditional Column” option under the “Add column” tab. You will see the window as in the below image.
The classes will be as below
- Class I: crop cover with <1%
- Class II: crop cover 1-2%
- Class III: crop cover 2-3%
- Class IV: crop cover 3-4%
- Class V: crop cover 4-5% and
- Class VI: crop cover >5%
See the resultant column created with the class information as we desired.
Using DAX to create new column from existing column Power BI
We can also use DAX for creating columns with substrings. The option is available under the “Table tools” in Power BI desktop. See the image below.
Now we need to write the DAX expressions for the exact substring you want to populate the column with. See the image below, I have demonstrated few DAX for creating substring from a column values.
Comparing to the original values you can easily understand how the expressions work to extract the required information.
Combining values of different columns
This is the last function which we will discuss in this article. Unlike fetching a part of the column values, we may need to combine the values of two columns. Such situation may arise in order to create an unique id for creating keys too.
Likewise here my purpose was to combine the state name and corresponding districts to get a unique column. I have used the COMBINEDVALUES() function of DAX to achieve the goal.
See the below image. I have demonstrated the whole process taking screenshots from my Power BI desktop.
I have tried to cover the steps in a single image. The original two column values as well as the combined value columns are shown side by side so that you can compare the result.
Final words
In this blog I have covered several options available in Power BI desktop for creating new column extracting values from other columns. We frequently face such situation where we need to create such columns in order to get desired analysis results or visualization.
I hope the theory explained along with the detailed screenshots will help you understand all the steps easily. In case of any doubt please mention them in the comment below. I would like to answer them.