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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
deaconb
Helper I
Helper I

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

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

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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