Forum Discussion
Solved
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...
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,_) ),",")
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.
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,_) ),",")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!!
