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.

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.

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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors