An alternative method to extract text from column (other than Text before delimiters,Length)

[Sujith_S]

I need to extract a text from a column. Please suggest a way other than the commonly used methods like "Text before delimeters" and "Length" from Extract option in Add column section.

Thanks

[v-alq-msft · Accepted solution]

Hi, @Sujith_S 

Based on your description, I created data to reproduce your scenario. In the example, we extract 'World' from each text. The pbix file is attached in the end. 

Table:

You may add a new step with the following m codes.

= Table.TransformColumns(#"Changed Type",{"Text",each let
lst = Text.PositionOf(_,"World",Occurrence.All),
l = List.Transform(lst,(x)=>Text.Middle(_,x,Text.Length("World")))
in 
if List.IsEmpty(l)
then ""
else Text.Combine(l)
}
)

Here are the m codes in 'Advanced Editor'.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kjNyclXCM8vSlGK1UHi5iDxdSACigooPCT1wamJYB5YQiEpNbG0pFIpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}}),
    Custom1 = Table.TransformColumns(#"Changed Type",{"Text",each let
lst = Text.PositionOf(_,"World",Occurrence.All),
l = List.Transform(lst,(x)=>Text.Middle(_,x,Text.Length("World")))
in 
if List.IsEmpty(l)
then ""
else Text.Combine(l)
}
)
in
    Custom1

Result:

Best Regards

Allan

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