Forum Discussion
Helper IIExtraction of multiple 7 characters text substrings starting with "O-" from string of text
Vladisam there you go
let Source = Web.BrowserContents("https://community.powerbi.com/t5/Power-Query/Extraction-of-multiple-7-characters-text-substrings-starting/m-p/2117172#M62409"), #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "DIV[id='bodyDisplay_8'] > DIV.lia-message-body-content > TABLE:nth-child(3) > * > TR > :nth-child(1)"}}, [RowSelector="DIV[id='bodyDisplay_8'] > DIV.lia-message-body-content > TABLE:nth-child(3) > * > TR"]), #"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}}), fx = let fx =(input)=> Web.Page( "<script> var x='"&input&"'; var b = x.match(/O[-][0-9A-Za-z]{5}/gm); document.write(b); </script>"){0}[Data]{0}[Children]{1}[Children] in fx, #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each fx([Column1])), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Text"}, {"Text"}), #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each List.Distinct(Text.Split([Text],","))), #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}), #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Text"}) in #"Removed Columns"
Ok check it out:
=Table.AddColumn("Orders", each List.Select(Text.Split([Column1], " "), each Text.StartsWith(_, "OI-")))
-- Nate
Microsoft EmployeeThis would be a good application of using RegEx to extract parts of your text string that match the desired pattern. This link shows how to use Web.Page() to do it, but it can also be done with a little bit of R or Python.
RegEx in Power BI and Power Query in Excel with Java Script – The BIccountant
Pat
Community ChampionVladisam just so you know, for more complex scenarios, regex might be the only choice left and I am so glad that regex can be natively run IN PQ.
Here is how that can be applied in your case.
Let's suppose your dataset is following
| Column1 |
|-----------------------------------------------------|
| LorempsumdolorsitametO123456eturadipiscingelitseddo |
| LorempsumdolorsitametO123456eturadipiscingelitseddo |
| LorempsumdolorsitametO12345turadipiscingelitseddo |
| LorempsumdolorsitametOabcd12piscingelitseddo |
| LorempsumdolorsitametOabcdpiscingelitseddo |
You want to extract exact 7 consecutive characters in length dynamically anywhere from the string starting with O
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8skvSs0tKC7NTcnPyS8qzixJzE0t8Tc0MjYxNUstKS1KTMksyCxOzsxLT83JLClOTUnJV4rVwaIvCQrI0A/XQbx9ChAAdW5iUnKKoRFx7oRrwFQeCwA=", 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}}),
fx = let fx =(input)=>
Web.Page(
"<script>
var x='"&input&"';
var b = x.match(/O[0-9A-Za-z]{6}/gm);
document.write(b);
</script>"){0}[Data]{0}[Children]{1}[Children]
in
fx,
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each fx([Column1])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Text"}, {"Text"})
in
#"Expanded Custom"
VladisamTotally agree - that seems to be very powerful. (BTW can it handle multiple substrings within same string?) I am lucky (for now) that substrings i need have spaces on both sides, but my users are unpredictable, so i am walking on thin ice.
This is waaay above my current level 😞 but definitely need learning.
And I need it in Service and I am not friends with R or Pythoon :(.
- smpa01
Vladisam few thing to clarify. ...the solution I gave you is neither coming from R nor from Python. I want to do everything natively as much as possible casue Python and R solution does not work in service. This is running javascript natively in PQ and handling regex through js natively in PQ.
Even though R has a similar library called stringR which I used to use for this sort of thing only to realize later that it would fail in service.
So be assured that whatever I am providing will fully evaluate in service as I have numerous regex extraction of mine running natively in my workspace.
Secondly, multiple substring I can try, but instaed can you please prepare a sample data, provide here and tag me back?