Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
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
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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