How to seperate particular text from a column and paste into another column.

[Anonymous]

Need to find "L0", L1" form column A and paste only the "L0", "L1" into column B.

Thanks..

[DOLEARY85]

Hi,

you can do this in power query using a custom column. Try this:

let
L0Index = Text.PositionOf([Column1], "L0"),
L1Index = Text.PositionOf([Column1], "L1"),
L0 = if L0Index <> -1 then Text.Middle([Column1], L0Index, 2) else null,
L1 = if L1Index <> -1 then Text.Middle([Column1], L1Index, 2) else null,
Result = if L0 <> null and L1 <> null then L0 & ", " & L1 else if L0 <> null then L0 else if L1 <> null then L1 else null
in
Result

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[Anonymous]

Hello @DOLEARY85, 

Thanks for your reply. I do face some Token EoF Error. Because of the the comma on the end of the first line. 

Could you please send me your custom coloumn formula on the query editor. That helps me a lot rather then normal copied text.

Thanks..!

[DOLEARY85]

You just need to add:

in
Result

to the bottom of the code you have there

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[DOLEARY85 · Accepted solution]

but if you need it here's the full code from the query:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sxLy7dS8DFQitWJVgpKTbNScPRxQXB8DJViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom.1", each let
L0Index = Text.PositionOf([Column1], "L0"),
L1Index = Text.PositionOf([Column1], "L1"),
L0 = if L0Index <> -1 then Text.Middle([Column1], L0Index, 2) else null,
L1 = if L1Index <> -1 then Text.Middle([Column1], L1Index, 2) else null,
Result = if L0 <> null and L1 <> null then L0 & ", " & L1 else if L0 <> null then L0 else if L1 <> null then L1 else null
in
Result)
in
#"Added Custom1"

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[Anonymous]

Thanks for your reply.!