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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
deaconb
Frequent Visitor

Extract text from string without delimeter

I'm trying to extract a document number from a string of text in power query. The document number always starts with "S" and contains 6 total characters including the "S", ex. S00001. The problem I'm facing is that there are no consistent delimiters which I can use to break down the text string. 

 

I also don't want to get the numbers from the string, keep the first 5, then add the "S" prefix back on because I don't trust that as a future-proof solution. 

 

 

Here is an example of the varying data: 

sample listing.png

 

 

 

 

 

 

 

Any help is greatly appreciated!!

1 ACCEPTED SOLUTION

@deaconb 

 

Try this custom column.

See attached file as well. it works with your sample data

 

=let mylist=List.Select(
    List.Transform(
    List.Transform({0..99999},each Text.From(_)),
    each "S" & (if Text.Length(_)<5 then Text.Repeat("0",5-Text.Length(_))&_ else _))
    ,
    each Text.Length(_)=6),
myname=[Name]
 in
 Text.Combine(
List.Select(mylist,each Text.Contains(myname,_) ),",")

extractee.png


Regards
Zubair

Please try my custom visuals

View solution in original post

4 REPLIES 4
v-juanli-msft
Community Support
Community Support

Hi @deaconb 

Here is a useful article for reference:

Text Extraction using the M Language with Power BI

 

It may be easier to do with DAX , could you accept?

 

Best Regards

Maggie

Thanks fr that link, Maggie. Unfortunately, that doesn't exactly get me the extraction methodology I'm looking for. I guess I basically am looking to define a data pattern, S##### and have it extracted from the string. 

@deaconb 

 

Try this custom column.

See attached file as well. it works with your sample data

 

=let mylist=List.Select(
    List.Transform(
    List.Transform({0..99999},each Text.From(_)),
    each "S" & (if Text.Length(_)<5 then Text.Repeat("0",5-Text.Length(_))&_ else _))
    ,
    each Text.Length(_)=6),
myname=[Name]
 in
 Text.Combine(
List.Select(mylist,each Text.Contains(myname,_) ),",")

extractee.png


Regards
Zubair

Please try my custom visuals

That script worked perfectly - thank you so much! I can't tell you how much manual data cleansing you just prevented from having to do weekly!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors