Forum Discussion
Helper VExtract substring from another column in Power Query based on delimiter
Hi, I have a column containing text 'PRJ-NNNNN-xxxxxxx-xxxxx' where N represents any single number. Is it possible to create a new custom column in Power Query to have only the number between two hyphen?
We need to have check if number is present , then copy to another column else put -99999
eg: PRJ-1234-Sample1-v1 - extract should be 1234
PRJ-1234-Test - 1234
PRJ-TEST - -99999
Test-Text1 - -99999
Hi manojk_pbi, check this:
Output
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSk23UggI8tI1NDI20Q1OzC3ISTXULTNUitWJVoKLh6QWl8BFQlyDQxR0wVyQOFCyosRQQVdBKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), Ad_Extracted = Table.AddColumn(Source, "Extracted", each Number.From(List.Select(Text.Split([Column1], "-"), (x)=> (try Number.From(x) otherwise false) is number){0}?) ?? 99999, type number) in Ad_ExtractedHi manojk_pbi ,
Thanks for reaching out to the Microsoft Fabric Community.
Thanks for your response, dufoq3 , You’re absolutely right. The expected output should be -99999, but the solution provided was returning 99999.
I went ahead and tested it, and modified the approach to return -99999 when no number is found. It should now meet manojk_pbi requirements.
Out Put :
Custom Column M Code :
= Number.From(List.Select(Text.Split([Column1], "-"), (x) => (try Number.From(x) otherwise false) is number){0}?) ?? -99999
If my response solved your query, please mark it as the Accepted solution to help others find it easily.
And if my answer was helpful, I'd really appreciate a 'Kudos'.
5 Replies
- dufoq3
Community Champion
Hi manojk_pbi, check this:
Output
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSk23UggI8tI1NDI20Q1OzC3ISTXULTNUitWJVoKLh6QWl8BFQlyDQxR0wVyQOFCyosRQQVdBKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), Ad_Extracted = Table.AddColumn(Source, "Extracted", each Number.From(List.Select(Text.Split([Column1], "-"), (x)=> (try Number.From(x) otherwise false) is number){0}?) ?? 99999, type number) in Ad_Extracted 
V-yubandi-msftCommunity Support
Hi manojk_pbi ,
Thanks for reaching out to the Microsoft Fabric Community.
Thanks for your response, dufoq3 , You’re absolutely right. The expected output should be -99999, but the solution provided was returning 99999.
I went ahead and tested it, and modified the approach to return -99999 when no number is found. It should now meet manojk_pbi requirements.
Out Put :
Custom Column M Code :
= Number.From(List.Select(Text.Split([Column1], "-"), (x) => (try Number.From(x) otherwise false) is number){0}?) ?? -99999
If my response solved your query, please mark it as the Accepted solution to help others find it easily.
And if my answer was helpful, I'd really appreciate a 'Kudos'.
- V-yubandi-msft
Hi manojk_pbi ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution we provided for your issue worked for you or let us know if you need any further assistance?
Your feedback is important to us, Looking forward to your response.
Thank You. - V-yubandi-msft
Hi manojk_pbi ,
We noticed we haven't received a response from you yet, so we wanted to follow up and ensure the solution we provided addressed your issue. If you require any further assistance or have additional questions, please let us know.
Your feedback is valuable to us, and we look forward to hearing from you soon.
Thank You.
- V-yubandi-msft
Hi manojk_pbi ,
We are following up to see if the solution we provided resolved your issue. If you need further assistance or have any additional questions, please let us know. Your feedback is important to us, and we look forward to your response.
Thank You.