Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
GavinR87
Helper II
Helper II

Pull last letter from a column

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 🙂

2 ACCEPTED SOLUTIONS
Mariusz
Community Champion
Community Champion

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


 

View solution in original post

Mariusz
Community Champion
Community Champion

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

 

View solution in original post

8 REPLIES 8
Mariusz
Community Champion
Community Champion

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


 

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
Community Champion
Community Champion

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

 

@GavinR87 

 

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

 

Regards

 

Victor 




Lima - Peru

Doh! That was it!

 

Thanks so much all - really appreicated. 

Pragati11
Super User
Super User

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: ex1.png
  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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

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

Hi @GavinR87 ,

 

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

@Mariusz thanks for the quick solution 🙂

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.