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

[Anonymous · Accepted solution]

Hi @manojk_pbi ,

Here I create a sample to have a test. You can do some transformation to add a custom column in Power Query Editor.

Duplicate Column > Split the copy column by Delimiter "-" > Add a cutom column to check data type in the second splited column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCgjy0jU0MjbRDU7MLchJNdQtM1SK1UESD0ktLoGLhLgGh4A5IFGgVEUJUHUsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column", "Column - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Column - Copy", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Column - Copy.1", "Column - Copy.2", "Column - Copy.3", "Column - Copy.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column - Copy.1", type text}, {"Column - Copy.2", type text}, {"Column - Copy.3", type text}, {"Column - Copy.4", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each let
_numbertype = 
Value.Is(Value.FromText([#"Column - Copy.2"]), type number)
in
if _numbertype then [#"Column - Copy.2"] else -99999),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column - Copy.1", "Column - Copy.2", "Column - Copy.3", "Column - Copy.4"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Custom", Int64.Type}})
in
    #"Changed Type2"

By Default:

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[manojk_pbi]

Thanks for the sample. Your solution and sample helped me lot.