Extract substring from another column in Power Query based on delimiter

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.