Power Query has various in-built functions which help us in transforming and preparing the data. To extract the first and last characters from string we can use Power Query text functions that is Text.Start and Text.End.
The Text.Start function returns a text value that is the first count characters of the text value text.
The Text.End function returns a text value that is the last count characters of the text value text.
Extract first characters from string
Text.Start functions extract the first characters (extract from left side) from given string.
Syntax of Text.Start function
Text.Start(text as nullable text, count as number) as nullable text
text:Â Given string.
Count: is the number of characters that you want to extract from the left side.
You can see, here we have a sample table contains a field Desc.
Let’s create a custom column named Desc1 which contains the first 3 characters of string Desc.
For this we need to use Power Query Text function Text.Start as shown below.
=Text.Start([Desc], 3)
Now you can see, newly created custom column Desc1 contains the first three characters of string Desc.
Extract Last characters from string
Text.End functions extract the last characters (extract from right side) from given string.
Syntax of Text.End function
Text.End(text as nullable text, count as number) as nullable text
text:Â Given string.
Count: is the number of characters that you want to extract from the left side.
Let’s create a custom column named Desc2 which contains the last 4 characters of string Desc.
For this we need to use Power Query Text function Text.End as shown below.
=Text.End([Desc], 4)
Now you can see, newly created custom column Desc2 contains the last four characters of string Desc.
Also Read..
Power Query : How to insert a substring into an existing text
