Power BITutorials

Split First & Last Name Using Power Query

1 Mins read

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.

BEFORE SPLITTING COLUMNS

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

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.

SPLIT COLUMN WINDOW

When you click on OK, you will get the expected result with names and surnames separated as shown in the below picture.

AFTER SPLITTING COLUMNS

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.

AFTER RENAMING COLUMN HEADERS OF SPLIT 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)

Related posts
AlteryxTutorials

Alteryx Hotkeys - Alteryx Keyboard Shortcuts

2 Mins read
Top 50+ Alteryx Shortcuts for Windows. Alteryx is popularly known as a Self-Service Analytics tool. Business users can build their data workflows…
AlteryxTutorials

Step By Step Guide to Learn Alteryx

6 Mins read
Alteryx Learning Path: The growth in technology has resulted in growth in understanding. In today’s world, humans – fellow businessmen know the…
Excel VBA CodeTutorials

VBA Code to Clean the Date Format

1 Mins read
When it is useful? Most of the time the most annoying problem is when the data is taken from ERP or other…

Leave a Reply

Your email address will not be published. Required fields are marked *