Extract substring from another column in Power Query based on delimiter

manojk_pbi — Tue, 11 Feb 2025 07:25:01 GMT

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

Re: Extract substring from another column in Power Query based on delimiter

Anonymous — Wed, 12 Feb 2025 02:26:11 GMT

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.

Re: Extract substring from another column in Power Query based on delimiter

manojk_pbi — Wed, 19 Feb 2025 02:37:42 GMT

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

topic Re: Extract substring from another column in Power Query based on delimiter in Developer

Extract substring from another column in Power Query based on delimiter

Re: Extract substring from another column in Power Query based on delimiter

Re: Extract substring from another column in Power Query based on delimiter