0:00
Hello everyone and welcome back
0:02
In this video we learn some text functions how they are done in Power Query
0:07
Now what are the different text functions we'll see in this video? If you see on the screen I have some trim, concatenate, upper, lower and proper
0:15
So these are the functions in Power Query we'll see. Now if you see the data on my screen, I have one first column as title, first name and last name
0:23
The data is not so good and what we want to do is we want to clean the data
0:27
For example, I want to remove the unwanted space. the unwanted spaces from this text
0:31
If you see, there are some unwanted spaces. Next, after removing those unwanted spaces
0:36
I want to combine these three columns, and after that, I'll be working on uppercase
0:40
lowercase, or propercase. Now, it is possible in Excel to do it with the help of functions
0:45
But without functions, how to do it with the help of button click? Let us see that practically
0:50
So, I have this data, and it is in the form of a table. So keep your cursor in the data anywhere
0:56
and then click on data tab, and then you see there's an option from table
1:01
Now if you're using Excel version 2016, you can see this option as from table
1:05
If you're using the lower versions, then you have to go to some other power query tab
1:09
and find out the option. Now, so I'll just keep cursor, data tab
1:13
and you have to click on from table because I'm extracting data from table
1:18
So click on from table and then the power query editor window opens here Now you see that a power query editor Same options you can see unwanted spaces in between So first thing I want to remove unwanted spaces So I click on the first column that is the
1:33
first name here, and then transform tab. All the text functions will be visible
1:39
in the text column. All the number functions will be visible here and the date
1:43
functions. Right now we'll focus on text functions. So select the first name
1:47
column and then if you see that's a format and there you can find out option
1:51
trim so I'll just click on this trim button and you see all the data is been
1:55
aligned properly same way I'll do it for last column last name format and then
2:01
trim so once I finish this you can see they have been trim whatever steps
2:07
you're applying all the steps are getting recorded in this section okay so if
2:11
any point of time if you want to go back you can just delete the step now after
2:16
removing the unwanted spaces I want to combine these three columns so make sure
2:20
you select the columns in a proper sequence. So I'll select the column here
2:25
Title, Control, click first name, control click last name. After selecting these three columns
2:34
after selecting these three columns, you can just go to same transform tab
2:38
and there's an option given as merge columns. So if you click on merge columns
2:42
we'll get a window here. This window will tell you, after merging these three columns
2:46
what should be the separator? In this case the separator should be space But in case if you want you can put comma you can put anything using this options Right now I just click on this space option and what should be the column name
2:59
This is an optional, but I'll try to write here the name of the column as full name, and then I'll just click on okay
3:07
So once you click on okay, all the three columns have been combined together with separator as space
3:13
And you can see the step is recorded here on the right end side. Now, after concatenating, I want to convert this text into uppercase, lowercase or proper case
3:23
So select the column and then transform tab. If you see in the format tab, we have the option as lowercase, uppercase
3:30
So if I click on lowercase, you see all the text has been converted into lowercase
3:35
Same way, I'll select the option as uppercase. And now, there's a proper case what we mention in Excel, but here it is known as capitalize each word
3:44
So if I click on this button, you see each alphabet of the word is capital here
3:49
Now, after doing all this transformation, I want to load this output data into my Excel
3:54
So the method is you can click on Home tab and there if you see the option, close and load
3:59
Just click on that button, this data would be loaded into your Excel file
4:04
So I'll just click on Close and Load. And you see, I've got a separate sheet, that is sheet 3, and you see the names are given here
4:12
That is the combined data here. What is the benefit of using this power query Whatever changes you make in this first sheet that will be applied into this output sheet Remember the output sheet is in green color table and the input data will be in the Brue Color format So if you see in the first sheet let
4:29
say the second row or the third row, that is Mrs. Mokesh Kumar. So what I'll do is, I'll try
4:34
to correct the name. I'll try to modify the name here. I'll write here some other name
4:44
space, space, unwanted space, and Lalwani. So I've written here name Mr. Pavan Lalwani, and when I go
4:50
to this sheet 3, if you see, the data will not change. So to change the data
4:54
you have to right click on the data anywhere on the table, and there if you see
4:58
there's a refresh button. So click on refresh, and then the data would be updated
5:03
Right. So if you see, now the data has been updated. So the benefit of using power queries
5:08
no need of remembering any function. Using button click, you can get the answers
5:12
and this process is completely automated. Now, at any point of time, if you think you made a mistake, you want to go back
5:19
On the right side, if you see, that's a workbook queries. You can simply double-click on this
5:23
button, and then you can open that Power Query Editor. You see, if I just double-click on this
5:28
button, the same Power Query Editor window opens, and then I'm allowed to do any type of changes
5:33
the same way what I've done previously. Okay? Now I'll just click on Home, and I can just click on
5:39
close and load. So I hope you have understood how to work with this text functions in Power Query
5:44
That's all for this video
