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
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"
Helper IITotally 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?
- smpa01
Vladisam did you mean mutiple substring like this? if yes, same soution will do the trick
If not, provide sample data
- Vladisam
Yes, this is what I am looking for - and it should be only unique values. But let me create sample data as well.
(Very cool Sales Calendar).
Sorry got confused with R - it's from Pat's post (above) regarding regex.