Forum Discussion
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:
Any help is greatly appreciated!!
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,_) ),",")
4 Replies
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,_) ),",")
