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
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors