Pull last letter from a column

[GavinR87]

Hi PowerBI community,

I am trying to create a custom column that takes the last alphabetic letter from another column.

So for example I have a column where values end in things such as "xxxxA" xxxxA1" "xxxxA2" "xxxxB" "xxxxB1" etc.

I want to make a column that would just pick up the last letter in that column and report that. I tried Text.End but that only takes the last charactor, not letter.

Grateful for any help 🙂

[Mariusz · Accepted solution]

Hi @GavinR87 

You can add a Custom Column in Power Query Editor with this syntax

=List.Last( List.Intersect( { Text.ToList( [Column1] ), {"a".."z"} } ) )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Mariusz · Accepted solution]

Hi @GavinR87 

You can add a Custom Column in Power Query Editor with this syntax

=List.Last( List.Intersect( { Text.ToList( [Column1] ), {"a".."z"} } ) )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[GavinR87]

Hi @Mariusz 

Thanks so much for the reply.

I have used your suggestion but for some reason it is outputting "null" in every cell of the column. It isn't giving me any errors just outputting null

Also I noted when I add the customer column, enter the formula, click ok, it changes to the below - not sure if that is the issue?

= Table.AddColumn(#"Renamed Columns4", "Carriageway", each List.Last( List.Intersect( { Text.ToList( [Detector] ), {"a".."z"} } ) ))

Apologies I am still a complete newbie at all this!

[Mariusz · Accepted solution]

Hi @GavinR87 

No this is what it suppose to do when you enter the formula into Add Custom Column, the issue is with the script I provided, forgot the important thing about M its case sensitive, use this instead it should do the trick.

= Table.AddColumn(Source, "Custom", each List.Last( List.Intersect( { Text.ToList( [Detector] ), {"a".."z"} & {"A".."Z"} } ) ) )

see the attached file for ref

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Vvelarde]

@GavinR87 

I think you probably had your letters in Uppercase. Replace a..z to A..Z

Regards

Victor 

[GavinR87]

Doh! That was it!

Thanks so much all - really appreicated. 

[Pragati11]

Hi @GavinR87 ,

If I understand your question correctly, your required output from input provided is - A, A1, A2, B, B1

Considering your inout column has always 4 'x' before the required value, i.e., 'xxxx'

You can achieve this in query editor.

1. Got to query editor
2. Click on your column with values - "xxxxA" xxxxA1" "xxxxA2" "xxxxB" "xxxxB1"
3. Under Transform tab --> Extract as shown below: 
4. A dialog will appear as soon as you select "Last Characters". Put there value as 2, as you want last two characters in your case.

This will give you desired output.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[GavinR87]

Hi Pragati,

Thanks so much for the quick reply!

I might not have explained myself quite properly.

I want the new column to pull the last letter only, so in my example the new column would just consist of either an "A" or a "B", regardless of whether the previous column ended in "xxA" "xxA1" or even "xxxxA1234", I would want the output of those 3 on the new column to be simply "A".

Hope this makes some sense 🙂

Thanks

[Pragati11]

Hi @GavinR87 ,

That's why it is important to explain your query in the right way.

@Mariusz thanks for the quick solution 🙂

Thanks,

Pragati