Sometimes, when you have a data table which includes full names of candidates in one column and you want to separate the full name into first name and surname in different columns.
Data Analytics Tricks in Power BI – Guidebook
You too can analyze data like a Data Scientist. No coding needed. No statistics needed.
Analyze & Visualize data using Power BI. (23 tricks in one book)
Split First & Last Name With Power Query
This is a common task, but most people may use complex formulas to perform this task and waste their valuable time. With Power Query, you can split the full name i.e.First name in one column and Surname in the second column within a few clicks. Here is an example to understand this concept.
Step 1: Select the column you want to split
Here, the Name column of the data table contains both name and surname which we want to separate out. To do this, we first need to select the ‘Name’ column as shown in the picture below.
Step 2: Split Column by Delimiter
Once you select the column, you can carry out the below steps to split the column-
Transform > Split Column > By Delimiter
Where a delimiter is a sequence of one or more characters used to specify the boundary between separate texts in data. It can be any character like bracket, comma or simply space.
Step 3: Select the Delimiter
After you click on the Split Column option, a Split Column window gets open which helps you to select the delimiter of your choice from the drop-down list. Here, we have chosen the ‘space’ delimiter because our data is separated by space. Also the split option ‘Each occurrence of the delimiter’is selected by default.
When you click on OK, you will get the expected result with names and surnames separated as shown in the below picture.
Step 4: Rename headers of the Split Columns
The split columns are named as Name.1, Name.2 etc. To rename the column headers, just double click on the header name and edit the name as you wish. We have renamed the column headers as Name and Surname respectively as shown below.
Data Analytics Tricks in Power BI – Guidebook
You too can analyze data like a Data Scientist. No coding needed. No statistics needed.
Analyze & Visualize data using Power BI. (23 tricks in one book)