Extract Text Using If Statement in Power Query

[a_88k]

Hi, 

I would like to extract some text but the data is not consitant and I can not use the extract function built into the system. I saw @v-huizhn-msft post but i could not replicate it on my end. I am also looking to create this in power query and not on the sheet. 

example data below i am looking to only extracat the R.J or RJ as again the data is not always consitently entered. 

Manor Road,ggzfnbfdzr - R.J506341.01

Forest Lodge Farm, Hythe S278 - R.J505919.01 - 6/3/7/243 - JZA3D

184 Bridge ads, test test, R.J506439.01

Manor Road,ggzfnbfdzr - R.J506341.01 - 6/3/13/384 - JZA83

Foxfield Grove (Leachman Way, Reeves Drive, Whittington Road, Crockford Drive) - Location. R.J505637.01 - 6/3/6/318 - JZV8A

[watkinnc · Accepted solution]

This works.  First replace any "R.J" with RJ, then add some "markers" to your text so you have delimters to split by:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Replaced Value1" = Table.ReplaceValue(#"Changed Type","R.J","RJ",Replacer.ReplaceText,{"Column1"}),
#"Replaced Value" = Table.ReplaceValue(#"Replaced Value1",".01",".01~~",Replacer.ReplaceText,{"Column1"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value","RJ","~~RJ",Replacer.ReplaceText,{"Column1"}),
#"Inserted Text Between Delimiters" = Table.AddColumn(#"Replaced Value2", "Text Between Delimiters", each Text.BetweenDelimiters([Column1], "~~", "~~"), type text),
#"Replaced Value3" = Table.ReplaceValue(#"Inserted Text Between Delimiters","~~","",Replacer.ReplaceText,{"Column1"})
in
#"Replaced Value3"

--Nate

[CNENFRNL]

let
    RE = (regex as text, str as text) =>
    let
        html = "<script>var regex = " & regex & "; var str = """ & str & """; var res = regex.exec(str); document.write(res)</script>",
        res = Web.Page(html)[Data]{0}[Children]{0}[Children]{1}[Text]{0}
    in res,

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY/LTsMwEEV/ZZQVSCap6zSPZaEqqAqbIlGJKAsTTxIL6pEcK6L9epwHsGJhyyPNPee6LINnacjCkaRibXttzHujrhbu4HjYrBIR83DFg4qVwZ4s9g4KUi3CXtozg6eL6xBe1mk27oc+sMl57gN+TCIRpdE6Fv59eNuK3QThWQz3Vo8IqXoGbkSOF5vzSSzyH+H/xcLfZouIi0h48mTKxFL3q9H4qeDR0oBwU6Csu7M0cJIXL0McsIed1QMyOHXaOW1aR2b2wYOl+qMhq+aVW48uqJZOkwmXnyYi/SvgD88m/2u2DarqGw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Str = _t]),
    Extracted = Table.AddColumn(Source, "RJ", each RE("/R\.?J.+?(?=\s|$)/i", [Str]))
in
    Extracted

[a_88k]

Hi 

Thanks, i have tried copying and pasting the above into the Query editor under custom column and get an error, do i need to account for some columns that do not have any R.J?

Ta