Extract substring from another column in Power Query based on delimiter

[manojk_pbi]

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

[dufoq3 · Accepted solution]

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-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.

[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 ,

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 · Accepted solution]

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'.

[dufoq3 · Accepted solution]

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