Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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!!
Solved! Go to Solution.
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!!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
15 | |
15 | |
13 | |
12 | |
11 |