cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JShafer26554
Frequent Visitor

Removing variable amount of characters from front of cell

Hi all!

 

I'm working with badge numbers, and some of our badges start with either one letter, two letters, or none at all.

 

Joe ShmoZZ1234125
Derrick DieterZ23412344
Paul Piper124244444
Aaron Iron124321441
Bowlsey Bollsly564234123

 

Is there any way that I can create something that recognizes the first few characters as letters and, if there aren't none, to leave the cell alone?

1 ACCEPTED SOLUTION
CNENFRNL
Super User III
Super User III

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcwxC8JADIbhvxIyd0kudbd00ang1uOGIgEPoyfXivTf26bf8C4PfDHitSjcHq+CDY4jcRDiFlMTsdda8/0JfdZF686OQcR5mL4GQ/44EQvLPqfzVMsbLlsOCkwi5NSVn826QlfMZls3b09y/GJKfw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Badge = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Num Only", each Text.Select([Badge], {"0".."9"}))
in
    #"Added Custom"

Screenshot 2021-06-24 195845.png

View solution in original post

4 REPLIES 4
negi007
Super User II
Super User II

@JShafer26554 you can create a column like below in your data

 

Column =
var chr_1 = MID('Table'[badge ],1,1)*1
var chr_2 = MID('Table'[badge ],2,1)*1
var chr_3 = MID('Table'[badge ],1,2)*1
var ctr = ISERROR(chr_1)+ISERROR(chr_2)

return
MID('Table'[badge ],1,ctr)
 
 
negi007_0-1624557604144.png

 




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos


Proud to be a Super User!

Follow me here


CNENFRNL
Super User III
Super User III

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcwxC8JADIbhvxIyd0kudbd00ang1uOGIgEPoyfXivTf26bf8C4PfDHitSjcHq+CDY4jcRDiFlMTsdda8/0JfdZF686OQcR5mL4GQ/44EQvLPqfzVMsbLlsOCkwi5NSVn826QlfMZls3b09y/GJKfw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Badge = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Num Only", each Text.Select([Badge], {"0".."9"}))
in
    #"Added Custom"

Screenshot 2021-06-24 195845.png

View solution in original post

Thanks! Worked like a charm and I was able to link the id with another table in order to pull their names into a bigger dataset.

@JShafer26554 You can also try below way

 

Column =
var chr_1 = MID('Table'[badge ],1,1)*1
var chr_2 = MID('Table'[badge ],2,1)*1
var chr_3 = len('Table'[badge ])*1
var ctr = (ISERROR(chr_1)+ISERROR(chr_2))*1

return
//chr_3
//ctr

MID('Table'[badge ],ctr+1,chr_3-ctr)
 
negi007_0-1624592838099.png

 




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos


Proud to be a Super User!

Follow me here


Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors