Power BI - Splitting a column
4K views
Oct 24, 2024
Power BI - Splitting a column
View Video Transcript
0:00
Hello friends and welcome to Tutorials Point
0:03
In this video we are going to see how to split columns in Power BI desktop
0:08
Alright, so let's get started off. So once we have imported the data using the Get Data tab, PowerBi make sure that different tables are being created
0:18
Now let's take for an example in one of the data table, in one of the column I have the name of some candidates
0:25
Right? So that name will include the first name and the last name
0:29
what I have to do is that I need a column in which one column should consist all the first
0:34
name and another column should consist the last name. So in order to split this column
0:40
Power BI desktop make sure that we have to use this query editor option. In the query
0:46
editor option, we can split this column very easily. And how we basically split this? There
0:51
are two ways of splitting the column. The first being we can split the column by a delimiter
0:57
or the second is we can split the column by the position deciding the position where you want the split to happen we can do the split accordingly now let take for an example for the delimiter now once we have a column in which i have certain delimiters delimiters such as commas equals to time semicolon or a colon or a space those are the delimiter types with the help of which we can split the column
1:22
So simply you have to right click on the column and click on split columns and out there you will be finding an option of delimiter
1:30
If you choose a delimiter and if you choose the proper delimiter, it will give you the column split very easily
1:36
The second method is splitting the column by position. So we have to decide this time whether you want the third character to be split or the fourth character
1:46
So we can be very much specific when we have to split the column according to the number of characters which I want
1:52
So this is used when I am sure that I just want to split the first three characters or the first four characters in a certain column
2:00
We'll be having a better understanding about how to split columns in our practical approach
2:06
Hello friends and welcome in this video we are going to learn how to split column in Power BI in order to do that I will move to the power query editor alright which we can see here right now
2:18
This is my power query editor and and under this you can see the queries which we can see out here on the X team left hand side of your screen that is my query spin I will move on to the product query and under the product query we will split this product column and how to do that I will help you in that so in order to split a column I will go to that particular
2:39
column header I will simply right click on it and after I right click on it there is
2:43
an option known as split column now out here you can see two options by delimiter
2:48
and by number of characters by limiter will help me if we have certain kind of
2:54
delimiter just like a hyphen a comma a space or a full stop so these are known as a
3:01
delimiter which can be used for a splitting purpose or we can also define the number of
3:07
characters that how many number of characters that you want in that particular
3:11
column I'll start off with the delimiter and I'll give you clarification on by
3:15
numbers as well so let me start off with delimators the moment I click on delimitors
3:21
another window will open up right in front of you and the first thing it will ask you for select or enter the delimiter so if I click on this
3:30
drop-down menu as I've told you that the delimiter can be anything it can be a
3:33
colon comma equals to sign semicolon space or tap you can also define your own delimiter by by clicking on custom if I click on customer and if and if I define that what is their delimiter it will take that as a delimiter and it will help you
3:50
to split your column as of now I will take here as a space as my delimiter
3:55
and I'll go to the left most delimiter now this is the left most delimiter we
4:01
have right most delimiter as well and there is something called us each occurrence of the delimiter let's say that if I have a sentence and if I want to
4:08
split that sentence and different columns so at that time I will use each occurrence of that delimeter
4:13
but as of now I will simply click on the left most delimiter and I simply hit
4:19
okay the moment I hit okay you can see the changes out here I simply hit okay
4:23
now as you can see this particular column has been divided into product one and
4:27
product two which in which the separator factor was space so the any character
4:32
which was before space has come into the product one column and the remaining
4:37
character in the product two column all right so the split has been done out here easily also you on the right hand side on the
4:44
applied step you can see the split column as delimiter and they have changed the
4:48
product type as well all right so this is how you split column using the delimiter now let me show you how it is done using the number of characters
4:57
specified number of characters so let me undo
#Business & Productivity Software